Excel problem with FV function.?
Information provided:
Annual tuition=$45,000; Rate=4.25%/12; Nper=15*12; Pmt=$375; and percentage of Tuition saved=FV/Tuition for four yrs.
Favorite Answer
=FV(0.0475/12,12*15,-325,0,0)
The monthly payment should be entered as a negative as the amount saved is an outflow.
Additionally, you should calculate the FV of the tuition with an inflation assumption for years 15,16,17 & 18. I would create a grid with your payments of $275-$875 in a column starting in A2.
Then I would list a range of inflation assumptions, say 3%-6% in 0.5% increments. in cells B1:H1
Enter your assumed rate of return in A1 4.75% and then enter this formula in cell B2 and copy down and right;
=FV($A$1/12,12*15,-$A2,0,0) /
( FV(B$1,15,0,-45000,0) +
FV(B$1,16,0,-45000,0) +
FV(B$1,17,0,-45000,0) +
FV(B$1,18,0,-45000,0) )
At monthly payment of $825 and 3% inflation they will achieve 74% of their inflation. Good Luck!
=fv(rate,number of payments, payments).
So, if the payment is $375, the function should be
=fv(.0425/12,15*12,375)
For some reason, Excel generates a negative result. As such, I would preface the function with the abs() function. This forces a positive result.
=abs(fv(.0425/12,15*12,275))
To compute the difference in options, I would have column A contain the range of different investment amounts and cell B1 look like =abs(fv(.0425/12,15*12,A1)).
Cell C1 would then be =B1/(45000*4).
This last part assumes that the price of tuition does not increase in 15 years. However, with a rate of 4.25%, you might have already accounted for this.
