4

I'm sure this has already been asked, but I've had difficulty finding the answer by searching. Most investment formulas I've found explained don't include a starting balance.

Given payment size, interest rate per period, inflation rate per period, existing investment balance, and a target size of investment, how can I calculate the number of payment periods still required to reach that size of investment?

EDIT: I'm looking for the mathematical formula, not an online calculator, that I can refer to while building a spreadsheet.

EDIT 2: If you'd like to give me a spreadsheet-specific answer, I'm using Google Sheets.

EDIT 3: Here's what I've been trying with NPER.

Google provides this documentation on their NPER function:

NPER(rate, payment_amount, present_value, [future_value, end_or_beginning])

  • rate - The interest rate.
  • payment_amount - The amount of each payment made.
  • present_value - The current value of the annuity.
  • future_value - [ OPTIONAL ] - The future value remaining after the final payment has been made.
  • end_or_beginning - [ OPTIONAL - 0 by default ] - Whether payments are due at the end (0) or beginning (1) of each period.

Intuitively, it seems like I should be calculating each parameter as follows:

  • rate - rate of return, either straight average investment return or maybe average investment return minus inflation.
  • payment_amount - amount I plan to pay into the investment per period.
  • present_value and future value - This is where it gets confusing. Should I put the current balance in present value or future value? In which should I put my target value? Either way I get the error "Scenario in function NPER is not possible."
Improve this question
2

3 Answers 3

Reset to default
3

The Finance functions in spreadsheet software will calculate this for you.

The basic functions are for Rate, Payment, PV (present value), FV (Future value), and NPER, the number of periods.

The single calculation faces a couple issues, dealing with inflation, and with a changing deposit. If you plan to save for 30 years, and today are saving $500/mo, for example, in ten years I hope the deposits have risen as well. I suggest you use a spreadsheet, a full sheet, to let you adjust for this.

Last, there's a strange effect that happens. Precision without accuracy. See the results for 30-40 years of compounding today's deposit given a return of 6%, 7%, up to 10% or so. Your forecast will be as weak as the variable with the greatest range. And there's more than one, return, inflation, percent you'll increase deposits, all unknown, and really unknowable.

The best advice I can offer is to save till it hurts, plan for the return to be at the lower end of the range, and every so often, re-evaluate where you stand. Better to turn 40, and see you are on track to retire early, than to plan on too high a return, and at 60 realize you missed it, badly.

As far as the spreadsheet goes, this is for the Google Sheets -

Type this into a cell

=nper(0.01,-100,0,1000,0)

It represents 1% interest per month, a payment (deposit) of $100, a starting value of $0, a goal of $1000, and interest added at month end. For whatever reason, a starting balance must be entered as a negative number, for example -

=nper(0.01,-100,-500,1000,0)

Will return 4.675, the number of months to get you from $500 to $1000 with a $100/mo deposit and 1%/mo return. Someone smarter than I (Chris Degnen comes to mind) can explain why the starting balance needs to be entered this way. But it does show the correct result. As confirmed by my TI BA-35 financial calculator, which doesn't need $500 to be negative.

Improve this answer
1
  • 1
    I tell my students that part of learning this type of thing is to use numbers where you know the answer or at least the range you should get. Then play with the variables. I'll return in a couple hours and kill the comments here, to avoid the long discussion. Glad to help, and to learn a bit about the google docs. Now, go tinker with the rates and see how decades of compounding gets crazy!
    – JTP - Apologise to Monica ♦
    Sep 5, 2016 at 15:00
3

Here's a formula; I had to go over to SEMath, use their MathJax to compose the answer and then paste this screen shot. As a result, I can't fix a typo: "ST" is the same as "St" enter image description here

Improve this answer
1

Fairly straightforward to match the result from the calculator soup link.

enter image description here

There is a formula to calculate n from the future value s (using natural logs)

n = Log[(d + r s)/(d + r x)]/Log[1 + r]

In Excel

=LN((1000+0.1*3068)/(1000+0.1*800))/LN(1+0.1)

This was derived as shown enter image description here

To calculate n from the inflation-adjusted future value si requires using a solver since an algebraic formula cannot be formulated. As demonstrated

enter image description here

Calculations done using Mathematica 7.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .