How to write a code for this Excel example?

Hello Friends, I was developing a cash flow dynamic table. But to write a code for one variable was hard because it follows unusual kind of pattern.
I want to write a code for this example for any given year (in this example for 4 years) on the same row…at the end of the year I want to get the total sum from year 1 upto the end of the year(in this example Year 4 End).

Year 1 begin Year 1 end Year 2 end Year 3 end Year 4 End
5% 2000 100 105 110.25 2315.25
Year 1 End =100= RC[-1]*RC[-2] Year 2 End =105= (RC[-2]+RC[-1])*RC[-3] Year 3 End =110.25= (RC[-2]+RC[-1]+RC[-3])*RC[-4]
Year 1 End =2315.25= RC[-4]+RC[-3]+RC[-2]+RC[-1]

Thanks a lot,
Biruk

Any particular reason you're using R1C1 reference style?

No particular reason.I used without the R1C1
Set tend = torigin.Offset(0, i)
For i = 1 To nyears
torigin.Offset(0, i).Formula = "=Sum(C24:C24.Offset(0,-i-1))*B24"

Then, at the end of the year(nyears) it will sum everything without multiplying with inflation
torigin.Offset(i - 1, 2).Formula = "=Sum(C24:C24.Offset(0,nyears))"

next
But the code I have it doesn't do as I wanted it.

I'm sure we can help you. What is the formula supposed to be doing, in words? I'm thinking there's no need to use VB code at all.

No particular reason.I used without the R1C1
Set tend = torigin.Offset(0, i)
For i = 1 To nyears
torigin.Offset(0, i).Formula = "=Sum(C24:C24.Offset(0,-i-1))*B24"

Then, at the end of the year(nyears) it will sum everything without multiplying with inflation
torigin.Offset(i - 1, 2).Formula = "=Sum(C24:C24.Offset(0,nyears))"

next
But the code I have it doesn't do as I wanted it.

I'm sure we can help you. What is the formula supposed to be doing, in words? I'm thinking there's no need to use VB code at all.

I want to do is I have two constant values in cell B24 and C24.The value in B24 is in percentage. Starting from D24 represent the number of years. In those cell I want to calculate the amount of money recovered. For example money recovered in year one will be Cell B24 * CellC24 the result will be shown in Cell D24. For year 2 it will be (Cell C24 +Cell D24 )*Cell B24 that will be shown in Cell E24. For year 3 it will be (Cell C24 +Cell D24 +Cell E24)*Cell B24 then it will be put in F24. Finally at the end of the year 4 it will sum from the Range(C24:F24) and shown in G24.

I need Vb because the number of years will change depending on the user.
Thanks a lot,
biruk

Put the following formula into cell D24: =$B24*SUM($C24:C24)

Then drag it over to cover D24 through F24. That should do it.

Make sure you have the dollar signs exactly right.

I still don't understand something in your original example.

Here are your numbers again:


Rate = 5% per annum.
Notional = 2,000
Year 1 End = 100 OK that's fine
Year 2 End = 105 OK that's fine
Year 3 End = 110.25 OK that's fine
Year 4 End = 2,315.25 I don't get this one.


I don't understand your Year 4 End number - why have you excluded the interest for year 4? I would have expected your Year 4 End amount to be 2,431.01 ???

It would seem that the first 100 is the interest on the 2000. The following years are interest on interest. Biruk did say this was an unusual pattern...

Hi Raucus,

Yes I understand that the interest is compounded. I said as such in my previous post and in my world that's really not unusual at all.

My question is why the interest in year 4 is excluded? I think only Biruk can answer that. It's mostly a curiousity because obviously Biruk can have whatever s/he wants: but if that was my 2000 being returned I would want to know why there is no interest on the final year. I make four years 2,431.01 and yet Biruk seemed to want 2,315.25 (my figure less the final year's interest).

I hope that explains my question a little more clearly.

yes, understood.

I was also wondering about Biruk's requirement that the formula work for "any given year". Does this mean "for any number of given years"? I had this sense because of Biruk's rationalisation for the use of code rather than a simple formula. The purpose of using code might be to avoid the user to be required to copy or otherwise manipulate Mill's proposed formula. Possibly the target range for the SUM() function should be a dynamic range?

Based on my understanding that 4 years would give 2431.01 and the table structure is:

. COLUMN: B C D E F G
ROW
23 RATE NOTIONAL YEAR 1 YEAR 2 YEAR 3 etc...
24 5% 2000
25


the formula I was going to suggest for D24 was going to be something like:

=($C24*(1+$B24)^RIGHT(D$23,1))-IF(ISBLANK(E24),0,($C24*(1+$B24)^(RIGHT(D$23,1)-1)))
which could then be copied + pasted throughout the table as required.

Probably it could be improved and chances are that, given the original question, this formula isn't what the OP was after anyway.

Either way, like Mill said, I tend to think that VBA is overkill here.

Hello Friends, I am sorry I was away from my desk for a couple of days. The rate is not an interest rate it is an inflation rate. The cash I have in cell C24 is a working capital it should be recovered in full at the end of the project maturity. Since inflation affected this working capital every year we need to calculate by how much it s affected in each year. Then, the amount recovered at the end of year will be the total sum rate without multiplying with inflation. I want to use Vb because the project end depend on the users so it will be dynamic. I hope I answered your question. Thanks Biruk

Hello Friends finally I solved the problem with you guys help. Thanks a lot. This the way I did it.
'............................
For i = 1 to nyears-1
Range("C24").Value = -1 * Worksheets("Financial analysis").Range("E13").Value
If Range("C24").Value = 0 Then
Range("C24").Offset(0, nyears).Value = -1 * Range("C24").Value
Else
Range("C24").Offset(0, i).FormulaR1C1 = "=R24C2*Sum(R24C3:RC[-1])"
Range("C24").Offset(0, nyears).FormulaR1C1 = "=Sum(R24C3:RC[-1])"
End If
Next
......
End Sub