L

#### 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
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

N

#### 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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia