+ Reply to Thread
Results 1 to 6 of 6

Help with TVM Spreadsheet

  1. #1
    Registered User
    Join Date
    03-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    2

    Help with TVM Spreadsheet

    Hey guys,


    I'm trying to create a spreadsheet that allows the user to enter the number of years to college, today’s tuition, estimated annual tuition growth rate and rate of return on investment. I want the spreadsheet to calculate the constant monthly investment needed to save enough to pay for college,

    I understand that it's going to be something like.

    =PMT(RATE OF RETURN/12, YEARS UNTIL COLLEGE*12, NPV(college costs)

    But I can't quite put my finger on how to write it correctly. Any ideas?

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Help with TVM Spreadsheet

    It needs to be done in two steps:

    First you have to work out the Future Value of the college fees and then you have work out how to get to that value.

    I worked out what was needed using a table and then found the formulas to match!

    Let me know if this works for you

    Regards
    Alastair
    Attached Files Attached Files

  3. #3
    MoneyMaker
    Guest

    Re: Help with TVM Spreadsheet

    Assuming that we are talking about annual tuition fee, you would have at first find the tuition fee at start of college from present and then find the net future value of four year tuition fees

    Once you have the net future value of four year tuition costs, that amount is called the sinking fund. You would like to have such amount at a future date while your monthly investment earns interest in this case 5% per annum compounded monthly

    To find the monthly payment you would have to multiply the four year future tuition fee by a sinking fund factor.

    The first of the following two tables shows you how to calculate this

    A B C D
    1 Current Tuition $10,000
    2 Tuition Growth Rate 2%
    3 Years Until College 10
    4 College Study Years 4
    5 Tuition Fee at Start of College $12,189.94 =tadFV(B2,,,B3,,-B1)
    6 Total Tuition Fee for 4 years $50,242.17 =tadFV(B2,,,B4,-B5)
    7 Target Return Amount $5,0242.17 =B6
    8 Investment Rate 5%
    9 Monthly Payment Required $323.55 =tadSFF(B8,,B3*12,,1/12,1/12)*B7

    This second table shows you average increase in tution amount at 2% growth rate

    2013 10000
    2014 10200
    2015 10404
    2016 10612.08
    2017 10824.32
    2018 11040.80
    2019 11261.62
    2020 11486.85
    2021 11716.59
    2022 11950.92
    2023 12189.94
    2024 12433.74
    2025 12682.41
    2026 12936.06
    2023-2026 50242.17


    I too had plans to completing my four years degree when I was 18, used to work 14 hours shifts on Gas Stations in Yonkers, NY during summer break to save for the next terms' tuition fee. In the final year of study, the Feds marched in to my life and turned it upside down

    Thanks to the Feds that I suffer from life long mental illness and my friend from London thinks I act like the guy in "A Beautiful Mind"

  4. #4
    Registered User
    Join Date
    03-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Help with TVM Spreadsheet

    Hey degree, your numbers were a bit off. I don't think you took into account the fact that he would be earning interest while in college.

    MoneyMaker, the whole sinking fund thing makes sense. Could you clarify a bit more?

    These are the numbers I'm working with (keeping them small so I can put them into a table and check)

    Year to college:2
    Todays Tuition:10
    Annual Tuition Growth Rate:2%
    Rate of Return:4%

  5. #5
    MoneyMaker
    Guest

    Re: Help with TVM Spreadsheet

    A B C
    1 Current Tuition 10
    2 Tuition Growth Rate 2%
    3 Years Until College 2
    4 College Study Years 4
    5 Tuition Fee at Start of College $10.404
    6 Total Tuition Fee for 4 years $42.88
    7 Target Return Amount $42.88
    8 Investment Rate 4%
    9 Monthly Payment Required $1.72
    10 Annual Withdrawals $11.36

    Monthly deposit of $1.72 will be required for 24 months to achieve a future balance of $42.88 which is the four year tuition fee for years 2015-2018

    Assuming that $42.88 is reinvested at 4% rate of return in 2015, you would be able to withdraw an annual amount of $11.36 to deplete all the funds in your account

  6. #6
    MoneyMaker
    Guest

    Re: Help with TVM Spreadsheet

    Quote Originally Posted by sawh7992 View Post

    MoneyMaker, the whole sinking fund thing makes sense. Could you clarify a bit more?

    Year to college:2
    Todays Tuition:10
    Annual Tuition Growth Rate:2%
    Rate of Return:4%
    SFF = i/[(1+i)^n - 1]

    Pmt = FV x SFF
    = 42.88 x i /[(1+i)^n -1]

    i = 4/1200 = 0.00333333333
    n = 2x12 = 24

    = 42.88 x 0.00333333333 /[(1+0.00333333333)^24 - 1]
    = 42.88 x 0.00333333333 /[(1.00333333333)^24 - 1]
    = 42.88 x 0.00333333333 /[1.0831429591590643354859195953988 -1]
    = 42.88 x 0.00333333333 /0.0831429591590643354859195953988
    = 42.88 x 0.040091588837440719379870923002263
    PMT = $1.72
    Last edited by MoneyMaker; 03-24-2013 at 02:32 AM. Reason: fixed an error in writing

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1