The Money Mustache Community
Learning, Sharing, and Teaching => Ask a Mustachian => Topic started 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!
-
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.
-
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.
-
Check out the NPer function for calculating "Months to FIRE".
-
See also the various calculations (that include the ones already mentioned) in the 'Misc. calcs' tab of the case study spreadsheet (http://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/).
-
Both very useful. Thanks for the input!
-
If you wanted to set a target date and future ammount, you can calculate the ammount you need to save with the PMT function.
Using the built-in financial functions is best for what you are doing, but if you do need a custom exponent function use the caret '^' character.
Here's the formula for the future value of a lump sum investment:
FutureValue=PresentValue*(1+RatePerPeriod)^NumberOfPeriods