College Tuition spreadsheet

[Lisa VH]

I am working on a worksheet and I need any help I can get.
This is the information I have to create my spreadsheet
and I am lost. Please help in any way you can. Please
email me at (e-mail address removed) if possible.
Here goes:
For the next 15 years, I plan to make monthly payment
deposits to a long-term savings account at a bank for
college tuition. The account pays 5.25% annual interest,
compounded monthly. I need to create a worksheet that uses
a financial function to show the future value (FV) of my
investment and a formula to determine the percentage of
the college's tuition saved. This is the information I
have: Out of State Annual Tuition= $50,000; Rate(per month)
=5.25%/12; Nper(number of monthly payments) =15*12; Pmt
(payment per period)=$400; and percentage of Tuition Saved
= FV/Tuition for Four Years; College for 4 years is
$200,000. I also need to create a data table that shows
the future value and percentage of tuition saved for
monthly payments from $175 to $775, in $50 increments.

So, I know some of what I need to do but I don't know how
to get started. The Out of State Annual Tuition is
throwing me off and don't I need to figure out a formula
for the cost of the tuition in order to figure out the
other information? I'm so confused. I would greatly
appreciate any help even just a step to get me started.
Thanks much! Lisa

 

[Norman Harker]

Hi Lisa!

Here's what you need:

A1: Interest
B1: 5.25%
A2: Term
B2: 180
A3: Required: 400000
A5: Payments
B5: Saved
C5: % of $400000
A6: 175
A7: =A6+50
Copied down to A18
B6:
=FV($B$1/12,$B$2,-A6,0,0)
Returns to B6: 47764.9138096203
Copied down to B18
C6:
=B6/$B$3
Returns to C6: 11.94%
Copied down to C18


The only problem I have is that the amount required ($400,000) is not
inflation adjusted.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.