# The Money Mustache Community

## Learning, Sharing, and Teaching => Ask a Mustachian => Topic started by: TexasRunner on December 18, 2017, 02:53:28 PM

Title: Need assistance, Spreadsheet Know-how - Exponentials
Post by: TexasRunner on December 18, 2017, 02:53:28 PM
Hello all,

Does anyone know how you can set up a defined exponential formula in excel.  Just as a basic example:  100,000\$ in year 1 compounded annually to year 10 (or whatever) at a defined rate with defined additional payments...?

This would be much easier with a formula than doing the Year 1 = XXX, Year 2 = Y1x0.07 + XXX...  etc.

What is the faster way that consistently works...?  I have to be missing something.

The goal is to change any of the parameters and automatically end with a defined number without having to use (or place) all of the annualized data anywhere.

Parameters:
-Initial Value
-Growth Rate
-Annual deposit
-Number of years

Result:
-Resulting value

If its something that absolutely requires the annualized numbers spread out, thats fine, it just seems there should be an exponential formula.

Thanks!
Title: Re: Need assistance, Spreadsheet Know-how - Exponentials
Post by: With This Herring on December 18, 2017, 03:08:26 PM
The formula you want is the Future Value formula, FV()

=FV([rate per period],[number of periods],[additional cash added per period],[starting value],[timing factor -> 0 for cash added at end of period (most common choice), 1 for cash added at beginning of period])

=FV(3%,10,-10000,-100000,0)
=\$249,030.43
You enter the \$10,000 per period and the \$100,000 starting amount as negative numbers because those are the amounts leaving your wallet, then the \$249,030.43 comes back into your wallet at the end, so that result is positive.

As is usual with spreadsheet programs, you can put all your inputs in separate cells to make them easier to change.

Because your rate and payment don't change, your payment structure is a simple annuity and this formula will work.  If rate and/or payment change, you will need to do a little more work.  Note that this can work for periods shorter or longer than one year.  If you are making monthly payments and your stated annual rate is 3% but it is compounded monthly, then you will need to use 0.25% as the rate in the formula and put in your monthly payment amount.
Title: Re: Need assistance, Spreadsheet Know-how - Exponentials
Post by: TexasRunner on December 18, 2017, 04:22:31 PM
That formula is perfect!!!

Not an annuity, but I'm needing to build in a 'time to FIRE' in my net-worth sheet.  The resulting savings values need to take into account the time for compounding (since savings compounded over 10+ years can be huge as we all know).

Now I can determine a "Month to FIRE" value based on the growth and current investments, excluding the potential for future raises or increased savings of any sort, and more accurately determine projections.

Thanks, I hadn't heard of FV before.  Didn't find it using my excel-googling skills either.  Good to know.

Once I'm done building the sheet, I'll post it for others to steal if they want.
Title: Re: Need assistance, Spreadsheet Know-how - Exponentials
Post by: Aeth on December 18, 2017, 09:26:32 PM
Check out the NPer function for calculating "Months to FIRE".
Title: Re: Need assistance, Spreadsheet Know-how - Exponentials
Post by: MDM on December 19, 2017, 06:00:24 AM