Future Value

[Devotedx77]

I have to setup a worksheet that claculates the future value and percentage
of tuition saved for monthly payments from $275-875 in increments of $50. For
the following data:

Annual Tuition $45,000, Rate = 4.25%/12, total payments of 15 years for 12
months/yr. I don't know how to setup the worksheet to calculate the
percentage of tuition saved. I setup the formula to calculate the FV as:
(reference the cell with the value - in my case is B15:B27) * ((1 +
Annual_Return / 12) * Years * 12)/Annual_Return/12. I don't know if my future
value calculation is correct but I have $97,402.57, $115,112.13, $132,821.69,
$150,531.25, $168,240.81, $185,950.37, $203,659.93, $221,369.49, $239,079.04,
$256,788.60, $274,498.16, $292,207.72, $309,917.28 as the future values based
on the various increments of $275-875 but that is as far as I've been able to
get. Can someone help?

 

[Leung]

Hi

What it mean for tuition saved? Can you please elborate more?

can you use the embeded FV function?

Leung

 

[Devotedx77]

What I mean by percentage of tuition saved is the percent of the $45,000*4
that is saved every month.

 

[Leung]

Hi

you said $45,000 is the Annual Tuition but why saving is calculated by
$45,000*4 ?

If you are using FV function below are the result.

What do you mean "saved" which 2 you want to compare?

Please elaborate more..




Annual Tuition 45000
Annual Rate 4.25%
period rate 0.35%
No. of period 180

Monthly Payment FV
275 ($69,076.08)
325 ($81,635.37)
375 ($94,194.66)
425 ($106,753.95)
475 ($119,313.23)
525 ($131,872.52)
575 ($144,431.81)
625 ($156,991.10)
675 ($169,550.39)
725 ($182,109.67)
775 ($194,668.96)
825 ($207,228.25)
875 ($219,787.54)

 

[Roger Govier]

Hi

I'm not certain what you are trying to calculate here.
Are you wanting to calculate what the payment needs to be to clear the
45000 in 180 months?
I put 45000 in cell B1 and 4.25% in C1
=PMT($C$1/12,180,$B$1,,0)
returns 338.53 as the monthly sum required.

If you are trying to calculate how many months it would take to clear
the debt at different monthly payments, then with -275,
-325, -375 in cells B3 :B15, enter the following in cell C3 and copy
down
=NPER($C$1/12,B3,$B$1)
This will give values of 2245, 191, 157 ...... 57
If you insert a row at row 5, and enter -338.53 in B5, the value in C5
will be 180 showing that the payment as calculated by the first formula
clears the loan in 180 months.