Hello, I'm having great difficulties figuring out how to calculate the following formula in excel. I will do my best to write it out and explain things. Say I buy 1 investment per month for 60 months. For each investment I am paid out principal and interest per amortization schedule. after the 60 month on each investment I'm paid out the remainder set amount. I am trying to compute and write the formula so I can show the amount per month I will take in. Keep in mind the amortization schedule amount will change each year (after 12 months) ie first month I buy 1 investment and I'm paid out $100, second month I buy 1 and I'm paid out $100 for the first investment and another $100 for that new investment, 3rd month I but one and am paid out a total of $300 for the 3 I own. However the payout on the first investmnet changes at the 13 month, the second months purchase payout amount changes at the 14 month per the amortization schedules. Then at the end of each 60 month term per investment that final payot occures.

I'm not sure if this will get you exactly what you want, but it might get you started. First, some assumptions: Initial investment = 10,000 Annual interest rates are 1%, 2%, 3%, 4% and 5%. The code will put these values in A1:A6 for the formulae to use. You can change these numbers in the code or in A1:A6 once the code puts them there. The code below will build a table that starts like this, showing the interest payments for 1 investment, then 2, then 3 etc. giving you the $100, $200 and $300 payments from your example. 100 100 100 100 100 100 In months 12 - 15 it will look like this, showing the increase in the interest payments as each "new" investment hits it's 13th month. 100 100 100 ... 200 100 100 ... 200 200 100 ... 200 200 200 ... There will be a similiar changes at months 26, 37 and 49 for each investment. After month 60, the interest for the first investment will stop and so on until in month 119 you will only have the 60th (final) interest payment for the 60th investment. Out in Column BJ, you'll see the total of the interest payments for each month. I did not deal with the return of principal since you didn't really explain how that works. Let me know how this works for you. Sub MyPayout() 'Clear sheet for testing Cells.ClearContents Application.ScreenUpdating = False 'A1 gets monthly investment amount Cells(1, 1) = 10000 'A2:A6 gets interest rate 1%, 2%, etc. For MyGrowth = 2 To 6 MyPercent = MyPercent + 0.01 Cells(MyGrowth, 1) = MyPercent Next 'Insert Sum formulae for monthly Interest payments Range("BJ1:BJ119").FormulaR1C1 = "=SUM(RC[-60]:RC[-1])" 'Initialize Variables RowChk = 0 FurstMonth = 1 InvCol = 2 NxtInv: 'Loop for inserting Interest Formula For InvMonth = FurstMonth To FurstMonth + 60 Cells(InvMonth, InvCol).Formula = _ "=IF((ROW()-" & RowChk & ")/12<=1,$A$1*$A$2," & _ "IF(AND((ROW()-" & RowChk & ")/12>1,(ROW()-" & RowChk & ")/12<=2),$A$1*$A$3," & _ "IF(AND((ROW()-" & RowChk & ")/12>2,(ROW()-" & RowChk & ")/12<=3),$A$1*$A$4," & _ "IF(AND((ROW()-" & RowChk & ")/12>3,(ROW()-" & RowChk & ")/12<=4),$A$1*$A$5," & _ "IF(AND((ROW()-" & RowChk & ")/12>4,(ROW()-" & RowChk & ")/12<=5),$A$1*$A$6,"""")))))" Next 'Increment Variables until we've done 60 FurstMonths RowChk = RowChk + 1 InvCol = InvCol + 1 FurstMonth = FurstMonth + 1 If FurstMonth > 60 Then GoTo Finished GoTo NxtInv Finished: Application.ScreenUpdating = True End Sub