+ Reply to Thread
Results 1 to 2 of 2

Calculating FV with incremental contribution increases.

  1. #1
    Registered User
    Join Date
    03-19-2020
    Location
    California, USA
    MS-Off Ver
    MS Office 365 business
    Posts
    1

    Calculating FV with incremental contribution increases.

    Ok am I missing something easy or is this actually complicated?

    I am trying to build a calculator specifically for financial planning for pilots. Pilots get a 16% non elective contribution to their 401(k)

    Easy to calculate the future value of the 401(k) if I know principle, contributions, interest rate and amount of years to retirement.

    It is not easy when the contributions will technically be different every year.

    How do I show the FV when the contributions (salary*.16) will be adjusted every year say by 1.5?

    Example: Year 1 >> Contribution = $220,000*.16=$35,200

    Year 2 >> Contribution = [$220,000+($220,000*.015)]*.16
    [$220,000+$3,300]*.16
    $223,300*.16 = $35,728

    As you can see, this makes the calculation of the FV a bit harder because the contribution is not consistent but it is consistently increasing.

    IS THERE AN EASY WAY TO CALCULATE THIS?

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Calculating FV with incremental contribution increases.

    Are the adjustments compounded or linear? That is, is the year 3 contribution 0.16*220,000*(1+2*0.015) or 0.16*220,000*(1.015)^2?

    If there were N years and the 401(k) becomes available exactly when the Nth year ends, the future value should be

    =0.16*220000*SUMPRODUCT(1+(ROW(1:N)-1)*0.015,(1+interest_rate)^(N-ROW(1:N))) for the former

    or

    =0.16*220000*SUMPRODUCT(1.015^(ROW(1:N)-1),(1+interest_rate)^(N-ROW(1:N))) for the latter.

    There are clever ways to handle both cases with annuity functions, but brute force is clearer, in my opinion.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating annual compounded rent increases
    By WarrenLF in forum Excel General
    Replies: 10
    Last Post: 05-11-2022, 12:44 AM
  2. Incremental Increases based on criteria
    By cjs578 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-16-2020, 10:29 AM
  3. [SOLVED] Incremental increases within formula
    By rush381 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2017, 05:41 PM
  4. [SOLVED] Increasing one cells value based on incremental increases in another
    By AndyPandy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2013, 08:45 AM
  5. Replies: 2
    Last Post: 03-22-2013, 02:20 PM
  6. Calculating interest for deposit with inperiodic contribution
    By agrigoryan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2013, 01:47 AM
  7. Calculating Compounding Rent Increases
    By smorkun in forum Excel General
    Replies: 6
    Last Post: 08-10-2009, 03:32 PM

Tags for this Thread

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