Author Topic: Compounding Interest Formula Help Wanted  (Read 1537 times)

rawwebdesign

  • 5 O'Clock Shadow
  • *
  • Posts: 1
Compounding Interest Formula Help Wanted
« on: January 04, 2014, 06:50:45 PM »
Hi Everyone,

I've been working on an excel spreadsheet to calculate at what date I'll be FI and am having a hard time getting the final formula to work.  Basically, I'm looking for a formula that can calculate:

"at 4% interest per year, how many monthly payments of $957 will it take to get to $1,139,000 assuming a starting amount of $60,090"

Does anyone have any experience with Excel... if so it would be greatly appreciated!

Thanks,
Brad

nwmohio

  • 5 O'Clock Shadow
  • *
  • Posts: 6
  • Location: Cleveland, Ohio
Re: Compounding Interest Formula Help Wanted
« Reply #1 on: January 04, 2014, 06:57:30 PM »
Using NPER (number of periods), it would be something like this (although I am compounding the annualized 4% monthly):

=NPER(RATE - interest rate converted from annual to monthly, PMT - monthly payment, PV - beginning balance, FV - ending balance)

=NPER(0.04/12,957,60090,-1139000)

= 424.6 months

= 35.4 years

Saverocity

  • Stubble
  • **
  • Posts: 151
Re: Compounding Interest Formula Help Wanted
« Reply #2 on: January 04, 2014, 07:21:06 PM »
In excel the way I would work it out (which may not be the prettiest) would be to use two columns: A and B. Before doing so I would work out the monthly value of 4% annual so I would calculate 4/12=0.3333333333%

I would put the starting amount in A1, and then in B1 put the following: =A*100.3333333333%
I would then copy/paste B1 into A2, then drag (using the crosshair in the bottom right) both columns down really really far until they hit where I wanted, that would give you a month my month appreciation.

Not pretty, but quick and easy.

MilStachian

  • 5 O'Clock Shadow
  • *
  • Posts: 89
  • Location: New England
Re: Compounding Interest Formula Help Wanted
« Reply #3 on: January 04, 2014, 09:01:07 PM »
Raw,
Great website for Excel financial functions.

http://www.excel-easy.com/functions/financial-functions.html