FV Function (Future Value Function)

[UsmanKhalid]

Hi,

I am having an issue in using FV (Future Value) function in Excel. I am suppose to be comparing four different plans. FV function works fine for three of them. I am hoping someone to have a look at my Excel sheet and let me know where I am making a mistake?

Regards

 

[joeu2004]

Sure. But without some details, there is nothing we can do.

First, how do you know you're making a mistake? That is, what error or erroneous result are you getting?

Second, exactly what are you trying to calculate? That is, explain the terms of the different plans as they relate to the FV parameters (period rate; number of periods; period payment, if any; PV; and timing of payments (beginning or end of period).

Third, show us the 3 FV formulas that work and the 4th FV formula that does not. If you reference any cells, show us their values or formulas, as well as any cells that they reference, etc.

That might be sufficient.

But if you __also__ want to share an Excel file, upload it to a file-sharing website (e.g. box.net/files) and post the public/share URL. First, test the download URL, being careful to log out of the file-sharing website in order to verify anonymous access.

 

[UsmanKhalid]

Sure. But without some details, there is nothing we can do.

First, how do you know you're making a mistake? That is, what error or erroneous result are you getting?

Second, exactly what are you trying to calculate? That is, explain the terms of the different plans as they relate to the FV parameters (period rate; number of periods; period payment, if any; PV; and timing of payments (beginning or end of period).

Third, show us the 3 FV formulas that work and the 4th FV formula that does not. If you reference any cells, show us their values or formulas, as well as any cells that they reference, etc.

That might be sufficient.

But if you __also__ want to share an Excel file, upload it to a file-sharing website (e.g. box.net/files) and post the public/share URL. First, test the download URL, being careful to log out of the file-sharing website in order to verify anonymous access.

Hi Joeu2004,

Thank you for your reply. The file is located at https://drive.google.com/file/d/14gDEBBF-sJjhnRMrFcAxL2P7dhKt1tkN/view

Basically I am trying to compare four different pension plans (A, B, C and D). One way to compare them is to calculate the future value (FV) on year by year basis. These year by year calculations can either be separate (i.e. $16800 for Yr1 and $17808 for Yr 2) or cumulative (i.e. show $16800 for Yr1 and then show $34608 (16800 + 17808) for Yr2).
Which ever method you select you are suppose to (and will get) $853,702 for Plan A. This can be crossed checked with "=FV(rate, nper, pmt, -NPV(rate,value1:value2),0)"

If you open the file you will see Plan A and D only. For Plan A, I have used two methods of using FV function. Both of them gives the same overall total which matches with the FV applied on NPV function.

When I apply the same FV functions on Plan D, I get different answers. My guess is the error (or incorrect values) is due to the fact that in Plan A (and B and C) the yearly amount remains the same but for Plan D the yearly amount is not the same. Its $12000 for the first three years and then it becomes $29760. I hope this is sufficient. Please let me know if further information is required.

 

[joeu2004]

My guess is the error (or incorrect values) is due to the fact that in Plan A (and B and C) the yearly amount remains the same but for Plan D the yearly amount is not the same.

Not in this case, with the way that you are using FV.

You're primary problem is with the time-frames (number of periods) over which you are using NPV v. FV. There is also a mechanical issue with the way that Excel NPV treats blank cells.

But you should have encountered and solved these issues in your solution for Plans B and C, which you imply worked fine.

So since this seems to be a class assignment, before I offer detailed comments on your solution for Plan D, I would like to see your solution for Plan B or C.

Please amend your Google drive file to include your solution for Plan B or C, or upload another file and provide its download URL.

 

[joeu2004]

My guess is the error (or incorrect values) is due to the fact that in Plan A (and B and C) the yearly amount remains the same but for Plan D the yearly amount is not the same.

Not in this case, with the way that you are using FV.

Correction.... That is, indeed, part of your problem. But not the major part.

 

[UsmanKhalid]

Not in this case, with the way that you are using FV.

You're primary problem is with the time-frames (number of periods) over which you are using NPV v. FV. There is also a mechanical issue with the way that Excel NPV treats blank cells.

But you should have encountered and solved these issues in your solution for Plans B and C, which you imply worked fine.

So since this seems to be a class assignment, before I offer detailed comments on your solution for Plan D, I would like to see your solution for Plan B or C.

Please amend your Google drive file to include your solution for Plan B or C, or upload another file and provide its download URL.

Amended File with solution for Plan B and C is at https://drive.google.com/file/d/1SG3_Gq-RFVp1lTPhaKBKBDuo-GFdW_pD/view?usp=sharing

I shall be waiting for your reply. For Plan D, problems appears at Year 4 (the cumulative for cell J13 should be $70255).

 

[joeu2004]

Sorry, I had a "brain fart" before. I disagree with your NPV calculation, but only in terms of apples-to-apples comparisons. The fact is: because of the way that Excel NPV treats initial empty cells (v. zero), your use of NPV and FV in rows 30 and 31 is consistent.

And with that in mind, you are correct: your primary mistake is treating the annual income as "pmt" instead of "pv" in your use of FV in rows 5 through 28. That fails when the annual income varies.

I think the following should work:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Plan A, D5
[/TD]
[TD]=FV($A$2,$B$4-B5,-C5,0,0)
[/TD]
[TD]=D4+FV($A$2,$B$4-B4,0,-C5,0)
[/TD]
[/TR]
[TR]
[TD]Plan B, F10
[/TD]
[TD]=FV($A$2,$B$4-B5,-E10,0,0)
[/TD]
[TD]=F9+FV($A$2,$B$4-B4,0,-E10,0)[/TD]
[/TR]
[TR]
[TD]Plan C, H13
[/TD]
[TD]=FV($A$2,$B$4-B5,-G13,0,0)
[/TD]
[TD]=H12+FV($A$2,$B$4-B4,0,-G13,0)[/TD]
[/TR]
[TR]
[TD]Plan D, J10
[/TD]
[TD]=FV($A$2,$B$4-B5,-I10,0,0)
[/TD]
[TD]=J9+FV($A$2,$B$4-B4,0,-I10,0)
[/TD]
[/TR]
</tbody>[/TABLE]

But that still does not work for Plan D (varied annual income). Frankly, I don't know why (yet). Another "brain fart".

The following does work.

It mirrors your use of NPV and FV in 30 and 31, but based on NPV the way that __I__ believe it should be calculated. That is, we should always discount to age 61 for an apples-to-apples comparison; thus, we must always compound that NPV over 25 years.

Aside.... By calculating the NPV in that manner, it is not necessary to calculate the FV. The better plan is the one with the larger NPV.

Enter the following into D5:

=IF(C5="", "", N(D4) + FV($A$2,$B$5,0,-PV($A$2,$B$4-$B5,0,-C5)))

Copy into D5:D28, F5:F28, H5:H28 and J5:J28.

The N() function avoids a #VALUE error in columns where annual income does not start in row 5.

I choose the cash flow signs so that PV or FV is always positive.

For brevity, I omit the type=0 parameter, since that is the default.

PS.... The SUM in J29 is meaningless. It should be deleted.

 

[joeu2004]

Enter the following into D5:
=IF(C5="", "", N(D4) + FV($A$2,$B$5,0,-PV($A$2,$B$4-$B5,0,-C5)))

Although useful for understanding the concept, it can be simplified algebraically to:

D5: =IF(C5="", "", N(D4) + FV($A$2,$B5-1,0,-C5))

which actually makes "good sense", when you think about it. (Klunk!)

Again, copy D5 into D5:D28, F5:F28, H5:H28 and J5:J28.

I should point out that the condition IF(C5="",...) assumes that only the initial annual incomes might appear to be blank cells.

 

[UsmanKhalid]

Although useful for understanding the concept, it can be simplified algebraically to:

D5: =IF(C5="", "", N(D4) + FV($A$2,$B5-1,0,-C5))

which actually makes "good sense", when you think about it. (Klunk!)

Again, copy D5 into D5:D28, F5:F28, H5:H28 and J5:J28.

I should point out that the condition IF(C5="",...) assumes that only the initial annual incomes might appear to be blank cells.

Thank you very much for the updated formula. I shall try to update my sheet with this one and update you soon. In the meantime, I have uploaded another amended version ( https://drive.google.com/file/d/1y1niIKjIIInvErolnOeADwrPLW5HXVN1/view?usp=sharing ). It is looking at the FV in "reverse" order (you will know what I mean when you open the file) and ALL the totals for Plan A, B, C and D matches with the sample answer (provided that sample answers are correct). I cannot explain it but its just a variation of FV function.

Thank again for your help.

 

[UsmanKhalid]

Although useful for understanding the concept, it can be simplified algebraically to:

D5: =IF(C5="", "", N(D4) + FV($A$2,$B5-1,0,-C5))

which actually makes "good sense", when you think about it. (Klunk!)

Again, copy D5 into D5:D28, F5:F28, H5:H28 and J5:J28.

I should point out that the condition IF(C5="",...) assumes that only the initial annual incomes might appear to be blank cells.

UPDATE!!!!

I think your formula and cross checking with NPV finally worked even for Plan D

https://drive.google.com/file/d/1kd0C6W9zIAt8EaZnlDVFPQpXXuSvBEQ9/view?usp=sharing

Thank you once again