FV Formula where the payments increase annually?

HarryFröhlich

Board Regular
Joined
Mar 25, 2003
Messages
116
Hi All! I am a new guy to the block and would appreciate some help concerning the following:

I need to calculate the proceeds of recurring payments at a fixed interest rate over a fixed term with one exception to the norm: Annually, the PAYMENTS grow bigger at a fixed rate (10% in the example).

It's like this:

Year 1: Payment: $1,200, interest 10%, term remaining: 20years.
Year2: Payment: $1,320 ($1,200 + 10%), interest still 10%, term remaining: 19 years of the initial 20.

The person will therefore be earning 10% compound interest on $1,200 for a full 20 years, 10% compound interest on $1,320 for 19 years, etc, until, in the last year, the payment will be $7,339.09. The person will earn 10% interest on that for 1 year only. What will the total payout be?

There has to be an easier way to calculate this than doing it the way I did here and hiding the calculations!

Hope to hear from you soon!

Harry
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
What answer did you get and ehat method did you use?

One way is

=Pmt1*SUMPRODUCT((1+0.1)^(ROW(OFFSET(A1,0,0,20,1))-1),(1+0.1)^(20-ROW(OFFSET(A1,0,0,20,1))+1))
 
Upvote 0
This looks like the PV of an exponentially growing annuity.

Try the attached formula (payments annually in arrear).

pvegann.gif
 
Upvote 0
Thanks you guys!

I'll respond to each successive reply in return. Here goes:

Juan, thanks for the input. I am currently doing the total calculation in the same way, but your answer differs from mine.

The interest earned on the initial payment of $1200 (IT IS PAID IN ADVANCE, I DID NOT SAY (sorry)) is the total earned over a 20 year span. The payment then increases by $120 in year 2. This increased premium will be paid for, and earn interest for, 19 years. Thereafter it increases again etc. etc.

Because my calculations differ from yours, I attach a spreadsheet called FV_Problem for you to check. It is protected without a password. I used Conditional Formatting to hide unnecessary cells, and the yellow ones at the top are not locked, because that is where data can be entered. My formulas may be incorrect, too. Please check!

Dave! Your turn! I have not had the opportunity to check your formula against my problem. Could it be possible that you run it against the attached file to see if it works, for I do not know which values to attach to which in the formula. (e.g. Which 0.1 is interest earned and which is growth in payment? What is in cell A1?) I hope that it is the right formula, 'cause you can see that it is a cumbersome sheet on which the only answer I need is in a single cell that is then written on another sheet. Please let me know!

John! I tried finding that formula last night, but the book was nowhere to be found! Thanks! Please just help me out on the following: What is t? Where is m? Is df the answer? And then I have this problem: The payments are annually in ADVANCE! How does that change the formula? Please let me know!

Kindest possible regards (ala Jack in the UK! Hi Jack!)

Harry
 
Upvote 0
Hi Again!

I cannot find a way to upload my Excel file! How do I do that without downloading the utility to show it like Juan did?

I downloaded the file, but elected not to download some Japanese text stuff it prompted me, thinking it not necessary. Now it does not work, saying that it could not load an object because it was not available on my machine and then it gave me an error message VBIDE = VBE6EXT.OLB.

I was not able to view Juan's formulas on his sheet, is that because of the same problem?

Anyway, please help!

My answer to the problem: $161, 460
 
Upvote 0
Sorry. Didn't explain formula and it was wrong anyway!

The above is a corrected version, with derivation. It's just the old annuity formula in a different guise. Just to restate it:

PV = 1/(r-g) * (1 - ((1+g)/(1+r))^t)

Note that it is an NPV. To convert to a future value just multiply the NPV by (1+r)^t.

Annuity formulas implicitly assume end of period cashflows. In your case, because payments are annually in advance, you need to add an extra years compounding ie multiply the NPV by (1+r)^(t+1).

If you decide that an abacus is better than all this formula stuff, I have to say I can't blame you! :oops:
 
Upvote 0
The formula that I provided provides the answer of $161,460.

=1200*SUMPRODUCT((1+0.1)^(ROW(OFFSET(A1,0,0,20,1))-1),(1+0.1)^(20-ROW(OFFSET(A1,0,0,20,1))+1))

"What is in cell A1?)"

A1 is just an address reference for the formula.

You could check math texts for a formula.

Possibly =20*1200*(1+0.1)^(20-1)*1.1


Edited for typo; I have great trouble with keyboard!
 
Upvote 0
Absolutely Brilliant Dave!

I'm glad that you get the same answer as me. I still have a problem with the input, however. I still need help!

You mention that A1 is just a reference for the formula. What does A1 do? What does the formula do to A1? Are there any values in A1?

Could you please replace these cell references in your formula?

Cell A1: The interest earned (10% per annum)
Cell B1: The annual growth in the payment (10%, as well)
Cell C1: The initial premium ($1,200 per annum)
Cell D1: The term (years) (20)

Thanks again!

Harry

(p.s. I have another problem that is very related to this one, as I'll quickly explain. (But I'll do another post on that and see what happens) In the scenario above, I provided you with the initial installment ($1,200). In the problem above, I needed to find out what the eventual payout would be should this initial payment be invested for a period of 20 years, but with the requirement that the payment was to increase every year by the 10%. In my calculations. I had to calculate the total principal paid (column 1, say)as well as the interest earned thereon (Column 2, say) I then added the two together to reach the answer both of us got.

Now here is the problem I face now: I do not actually know what this initial payment will be! What I do know, is the total value of the PRINCIPAL (interest excluded) that has to be paid over this term of 20 years. I use Solver to ask Excel to calculate the value of the initial payment IF it is to increase by 10% each successive year FOR the period of 20 years. And only then, once I have that payment calculated by Excel, do I run this other series of calculations to figure out what the value would be had the guy invested these payments at the rate of 10% (e.g.).

I found an example macro for Solver, but I cannot find detail, and the description is very vague. How can I use a (your?) formula to find the payment to start with?

I am going to post the above seperately, Dave, but in case I find it difficult finding the time, answer on this topic if you can help!

Thanks to all!

Harry
(p.s. U tall king about thaipos! English is not even my first language, and I have 2wo, NO! 3ree! thumbs to contend with on the qeebord as well! (I looked when you mentioned, but did not find any Dave!) Have a great evening!)
 
Upvote 0
1. You can edit the formula and replace the numbers with cell references.


2. If you do not want to use the offset(....), use

=1200*SUMPRODUCT((1+0.1)^({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}-1),(1+0.1)^(20-{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}+1))

ROW(OFFSET(A1,0,0,20,1)) is used to provide the number sequence 1 .. 20.


3. Check Help for Financial Functions.

consider

=PMT(0.1,20,0,68730,0) yields $1200.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top