Loan or Investment Formulas
revised 19 Jul 2015
revised 19 Jul 2015
Summary: Compound interest can work for you or against you. Whether you’re taking out a loan or making an investment, either way it’s the same set of formulas. This page gives you the formulas, shows where they came from, and works through lots of examples.
Copying: You’re welcome to print copies of this page for your own use, and to link from your own Web pages to this page. But please don’t make any electronic copies and publish them on your Web page or elsewhere.
“Don’t make me wade through all that algebra,” I hear you yell. “Just give me the formulas!” Here ya go; there’s also an Excel workbook available for download. (These formulas weren’t brought down from heaven by an archangel. If you want to see the derivations, which aren’t really all that hard, they’re given later in this page.)
All formulas apply when payments are made at the end of each period, and please understand that results are approximate. The variable names should be pretty straightforward, but I explain them below.
|(1) Loan balance after n payments have been made||derivation|
| example 1
|(For a savings account or other investment, just change the first minus sign to a plus.)|
|(2) Payment amount on a loan||derivation|
| example 2|
|(3) Number of payments on a loan||derivation|
| example 3|
|(4) Original loan amount||derivation|
| example 5|
|(The original loan amount is also called the present value of an annuity or present value of a stream of payments.)|
|(5) Payment amount to reach an investment goal||derivation|
|(6) Number of payments to reach an investment goal||derivation|
|(7) Interest rate||derivation|
But this problem is normally solved by iterative methods.
| example 6|
Fairly often in the newsgroup
queries like this one are posted:
I was hoping someone could provide me with the formula for determining compounding interest problems. I have a formula, but I’m not sure what numbers plug in where. Help. This is as far as I understand:
Another perennial favorite:
How much must I pay into a savings account every month to accumulate $200,000 in ten years, if the account pays 5%?
Finally, here’s a question I hope you get to ask one day:
I just won the lottery. The prize is $26 million, paid as $1.3 million a year; or I can take a lump sum of $16 million. What should I do?
These are various forms of the present value, future value, and annuity problems. They all have to do with an investment or a stream of payments, and how the value changes over time. To update the old saying slightly, “A bird in the hand is worth 1.06 in the bush.”
The general idea is that there are two things going on at the same time in a loan or an investment:
This page will develop the formulas to solve all sorts of present-value or future-value problems. These cover loans, savings accounts and other investments, mortgages, and annuities. As you’ll see, though there are lots of names for these problems they’re really all the same thing looked at from different angles.
Because loans seem to be the most popular problems, I’ll start with them. After that, I’ll adapt the formulas for other sorts of future-value problems. For example, a loan is the mirror image of making an initial big deposit in a savings account and then drawing out a constant sum every month until there’s nothing left. The total of all payments/withdrawals will eventually reach the total of the initial loan/deposit plus accrued interest.
The formulas and examples in this page are all valid algebraically, but they should be considered as just approximations financially.
For example, you might compute a payment amount of $65.4321. Obviously it’s not possible to make a payment to greater precision than one cent, so you’ll be paying either $65.43 or $65.44 a month. Either way the next month’s balance will be slightly different from what you might compute from the formula. Over time, these differences may either grow or cancel themselves out.
Therefore, in a real loan situation you should probably expect to see minor discrepancies from the results of the computations here.
Beyond mere rounding, you may also find differences because financial institutions apply traditional or creative accounting techniques such as the “rule of 78” or the 360/365 rule. Your best protection is to read all documents carefully and, if necessary, use the formulas on this page to check the figures you’re given.
|A||the loan amount (the principal sum) or initial investment|
|B_n or Bn||(pronounced “B sub n”) the balance after n payments have been made. After the last payment has been made, B_N is zero.)|
|F||the future amount accumulated by a stream of payments|
|i||the interest rate per period, not per year|
(For instance, if the loan payments are made monthly and the interest rate is 9%, then i = 9%/12 = 0.75% = 0.0075.)
|n||the number of time periods elapsed at any given point|
|N||the total number of payments for the entire loan or investment|
|P||the amount of each equal payment|
If you haven’t spent much time in
may be puzzled by the caret (^) in some of the formulas that follow.
I’m using it to indicate exponents: x^y is x to the power y, so the
caret is pronounced “to the power of”. Yes,
HTML does support superscripts; but not everyone’s browser renders
them clearly, and I understand that some systems for the visually impaired
also don’t distinguish them.
All of these problems assume that the payment amounts are all equal, except possibly for an initial or final payment that is different. It’s possible to solve problems with unequal payments, but the calculations are quite a lot messier; for that sort of problem a spreadsheet is your best tool.
The method I’ll use for solving these problems is to come up with a formula for B_n, the loan balance after n payments. Then a few transformations of that formula will show how to solve for the other variables.
To work out that formula for B_n, what I’ll do is figure the balance at the end of the first period, second period, and maybe a couple more, and look for a pattern.
When the loan is first taken out, no payments have been made, so the loan balance is the same as the loan amount:
B_0 = A
What happens at the end of the first period? Interest has accrued. The interest rate per period is i, and the balance is B_0, so the accrued interest is i times B_0, which equals iA; this gets added to the loan balance. On the other hand, the payment P is subtracted. Therefore:
B_1 = A + iA - P = A(1+i) - P
What happens to the loan balance at the end of the second period? We add in the interest on the previous balance, which is i times B_1, and subtract the next payment P:
B_2 = B_1 + iB_1 - P = (1+i)B_1 - P = (1+i)[ A(1+i) - P ] - P = A(1+i)^2 - P(1+i) - P
This process repeats at the end of each period: add in the accrued interest and subtract the payment. I won’t show all the steps for B_4, but you should be able to work them out for yourself.
B_3 = B_2 + iB_2 - P = (1+i)[ A(1+i)^2 - P(1+i) - P ] - P = A(1+i)^3 - P(1+i)^2 - P(1+i) - P B_4 = A(1+i)^4 - P(1+i)^3 - P(1+i)^2 - P(1+i) - P
Now we have enough information to write a general expression for B_n, the loan balance after the nth payment. Look at the first term in B_0, B_1, B_2, B_3, B_4: it’s always A times the nth power of (1+i). (Remember that (1+i)^0 = 1, so A = A(1+i)^0.) This gives us for a start:
B_n = A(1+i)^n - something
The first part of this formula is known as the future value of the principal sum A. It reflects the fact that money grows in value over time. The second part, the “something”, is the effect of the payments. Their value also grows over time; or, to put it another way, a payment made early has more effect toward paying off the loan than the same amount paid later.
Now let’s look at the “something”, the remaining terms. We can summarize them as
something = P(1+i)^(n-1) + P(1+i)^(n-2) + ... + P(1+i) + P
This is a geometric series, as becomes clear if we write in the implied exponent of 1 and use the fact that P = P(1+i)^0:
something = P(1+i)^(n-1) + P(1+i)^(n-2) + ... + P(1+i)^1 + P(1+i)^0
The general summation formula is . Here we have a = P, r = (1+i), N = (n−1), and therefore
[ (1+i)^(n-1+1) - 1 ] [ (1+i)^n - 1 ] SUM = P --------------------- = P --------------- [ (i+1) - 1 ] i P SUM = --- [(1+i)^n - 1] i
which means that the complete formula is
This master formula relates the balance B_n after n periods, the original amount A, the payment P, and the interest rate i per period. It’s also used in the Excel workbook that accompanies this page.
Everything else will derive from that master formula. So if you need to memorize formulas, this is the only one you need to memorize.
If you know the interest rate i, loan amount A, and payment P, you can use equation 1 to find the current balance remaining after n payments. This is sometimes called the payoff amount. (In bygone days, the actual payoff amount was frequently greater than that, owing to the “rule of 78”. But in the U.S. in recent years the truth-in-lending laws have made the rule of 78 and other prepayment penalties much less common.)
You have a $18,000 car loan at 14.25% for 36 months. You have just made your 24th payment of $617.39 and would like to know the payoff amount.
Solution: 14.25% interest a year is 1.1875% per month, so i = 0.011875. Substitute P = 617.39, A = 18000, n = 24 into equation 1 to solve for the current balance:
B_n = A(1+i)^n − (P/i)[(1+i)^n − 1]
B_n = 18000*1.011875^24 − (617.39/0.011875)(1.011875^24−1)
B_n = 6866.97
After 24 months, which is 2/3 or 67% of the loan term, you’ve paid off only 62% of the loan. That’s the effect of the accrued interest, and it’s even more lopsided for longer loans, as you’ll see in example 2.
Equation 1 showed how to find the current balance, or payoff amount, on a loan. But more likely you want to know what the payment amount will be for a certain number of payments, or how many payments of a certain amount will be required. The most obvious times when you’d ask these questions are when you’re buying a car on a 3-year or 4-year loan (or longer), or a house on a 30-year loan.
The next two sections show how you can find both the number of payments N and the payment amount P by doing some algebra on equation 1. When the loan is paid off, the remaining balance B_n = 0, so set B_N to 0 in equation 1 and solve for either P or N.
Let’s solve first for P.
B_N = 0 after the final (Nth) payment at the end of the loan, and therefore
P 0 = A(1+i)^N - --- [(i+1)^N - 1] i P --- [(1+i)^N - 1] = A(1+i)^N i P[(1+i)^N - 1] = iA(1+i)^N i A (1+i)^N P = ----------- (1+i)^N - 1
Multiply top and bottom by (1+i)^-N to simplify:
There used to be published tables of (1+i)^-N for many increments of i and N. Nowadays, of course, we just punch up the numbers on the calculator, and the answer pops out. You can also use the Excel workbook that accompanies this page.
You are buying a $250,000 house, with 10% down, on a 30-year mortgage at a fixed rate of 7.8%. What is the monthly payment?
Solution: 30 years is 360 months, and the monthly interest rate is 7.8%/12, or 0.0065. The loan amount is 90% of $250,000, which is $225,000. Substitute in equation 2:
P = iA / [1 − (1+i)^-N]
P = 0.0065*225000 / [1 − 1.0065^-360]
P = 1619.708627 → $1619.71 is the monthly payment
The bank will normally round a loan payment up to the next penny, or even the next dollar, leaving the last payment to be slightly smaller than the rest.
By the way, it can be interesting (and horrifying) to compute B_12, the loan balance after the first year’s payments on that loan. Using equation 1, you’ll find that
B_12 = 223044.55
You’ve made payments of $19,436.52 (12 times 1619.71), but not even $2000 of that went to the principal of the loan. It’s a shocking fact that every homeowner faces at some point in a mortgage: during the first year practically all your payments go to interest.
Now you know to calculate the payment amount when the number of payments is predetermined. What about the other direction?
Suppose you predetermine the payment amount and need to know the number of payments? This is a less common situation, but here are two real-life examples:
To determine the number of payments for a fixed payment amount, you could start at equation 1 again, but it’s a shade easier to start from equation 2: . Solve for N:
P 1 --- = ------------ i A 1 - (1+i)^-N i A --- = 1 - (1+i)^-N P i A (i+1)^-N = 1 - --- P
Take the log of both sides:
-N*log(1+i) = log(1-iA/P)
And there you have it: the number of payments P on a loan of amount A with interest rate i. (You can use any logarithm base, as long as both logs use the same base.) You can also use the Excel workbook that accompanies this page.
Aunt Sally offers to lend you $3500 at 6% for that new home theater system you want. If you pay her back $100 a month, how long will it take?
Solution: 6% per year is 0.5% per month, or 0.005. P = 100 and A = 3500. Substitute in equation 3:
N = −log(1−iA/P) / log(1+i)
N = −log(1−0.005*3500/100) / log(1.005)
N = −log(0.825)/log(1.005)
Any base of logarithms will give the same final answer, so use base-10 logs:
N = −(−0.083546)/(0.0021661)
N = 38.57
You’ll pay Aunt Sally 38 payments of $100 each, and then a smaller final payment to finish the loan.
How much is that final payment? First find B_38, the loan balance after 38 payments. Use equation 1 to find that
B_38 = 56.83
You can either include that in your 38th payment or pay it separately as a 39th payment.
If you include it in the 38th payment, you will pay 37 monthly payments of $100.00 and a 38th payment of $156.83.
If you make it a separate payment at the end of the 39th month, you’ll owe Aunt Sally 0.5% interest on the $56.83 for that month, so your final payment will be $56.83+(0.005×56.83) = $57.11. In that case you’ll pay Aunt Sally 38 payments of $100.00 and a 39th payment of $57.11.
Suppose you pay Aunt Sally $15 a month instead of $100. How long will it take to pay off the loan then?
Solution: As before, 6% a year is 0.5% = 0.005 a month.
N = −log(1−iA/P) / log(1+i)
N = −log(1−0.005*3500/15) / log(1.005)
N = −log(−0.17)/log(1.005)
What’s up with that log of a negative number? Quite simply, at $15 a month you’ll never pay Aunt Sally off. The monthly interest on $3500 at 6% is 0.06×3500/12 = $17.50, so with a $15 payment your debt grows instead of shrinking.
The downloadable Excel workbook displays an error message if you enter impossible numbers like these into the computation of number of payments.
You have $15,000 in a 5% savings account, which is compounded monthly. How long will it take to run down the account if you withdraw $100 a month?
Solution: This is a straight loan from you to the bank: A = 15000; i = 5%/12 = .004167; P =100, and you need to find N. Use equation 3:
N = −log(1−iA/P) / log(1+i)
N = −log(1−.004167*15000/100) / log(1.004167)
N = 235.89
You can take out $100 a month for 235 months (almost 20 years). Since N is not a whole number, there will be a little money left in the account after your 235th withdrawal. You can use equation 1 to find that B_235 = $88.56. So at the end of the 235th month you can close the account by withdrawing $188.56, or you can take only the $100 and close out the account at the end of the 236th month. In that case you will have earned interest of (5%÷12)×88.56 = $0.37, so you’ll receive $88.93 at the end of the 236th month.
By the way, the minus sign in equation 3 may look a bit strange: how can a number of payments be negative? Actually, it’s not. Since 1−iA/P is between 0 and 1, its logarithm will be negative, so negating it as in equation 3 gives a positive number N.
Huh? What? Don’t you know that? Well, maybe not. You may know what monthly payment you can afford, and about what interest rate you’d be paying. And from that, you want to know how big a purchase you can afford.
Again, start with equation 2: . But this time solve for A, the original loan amount:
i A ------------ = P 1 - (1+i)^-N A P ------------ = --- 1 - (1+i)^-N i
You’re looking to buy furniture for your living room. You can afford to pay about $60 a month over the next three years, and your credit card charges 16.9% interest. How much furniture can you buy?
Solution: P = 60, N = 36, and i = 16.9%/12 = 1.4083% = 0.014083. Use equation 4:
A = (P/i)[1 − (1+i)^-N]
A = (60/.014083)[1 − 1.014083^-36]
A = 1685.25
If you put the purchase on your credit card, at 16.9% interest you can afford to buy about $1685 worth of furniture.
Example 5a: But you have your eye on a set that’s on sale for $1850. The saleswoman offers you a store credit card with a special promotional rate of 12% for three years. Now can you afford the furniture?
Solution: P = 60, N = 36, i = 12%/12 = .01. Use equation 4 again:
A = (P/i)[1 − (1+i)^-N]
A = (60/.01)[1 − 1.01^-36]
A = 1806.45
At 12% interest, you can afford $1806.45,`which`is`not`quite`enough.`But` fortunately the saleswoman works on commission and agrees to knock an extra $50 off the price. (I make up these stories, so I can throw in a happy ending if I want!)
This one, unfortunately, is trickier. Mathematicians say that there is no closed-form solution for interest rate, meaning that no straightforward formula exists to provide an exact solution with i on the left and other variables and functions on the right, in a finite number of steps. You can still find i, but you have to work for it. Here are several methods to choose from:
If you really want to use a formula, you can evaluate terms of an infinite series.
If you have access to suitable software or an advanced calculator, you can solve equation 2 numerically. Substitute the known quantities A, N, and P and have your calculator or software solve for i.
If you have a graphing calculator, you can plot equation 2 after substituting in the known values of A and N, and see what i value gets P close to the value given for the loan.
If you don’t have a graphing calculator, you might want to download the freeware MathScribe program.
The traditional solution technique is an iterative method, which is what mathematicians call a series of guesses that get closer and closer to the true answer. Newton’s Method or the Newton-Raphson Method is one of the best known; it’s described below.
You can also use the Excel workbook that accompanies this page.
Newton’s Method has the advantage that it’s very fast. More precisely, Newton’s Method finds the interest rate very quickly if it can find it at all. Newton’s Method can fail if your initial guess for the interest rate is too outlandish, but in the real world that’s not a problem because you usually have some idea of the actual interest rate.
You can read all about Newton’s Method at Mathworld. But briefly, Newton’s Method requires you to rewrite one of the equations involving interest rate so that the right-hand side is 0; the left-hand side is then called f(i). You then take the derivative, f′(i), and make an initial guess at the interest rate. From that guess i you form the next guess inew by the equation
inew = i − f(i)/f′(i)
and you repeat until there’s no change from one guess to the next. Usually this happens in less than half a dozen iterations.
Starting with equation 2 (shown at right), I subtract the right side from the left and clear fractions, then differentiate to get
f(i) = P − P (1+i)^-N − iA
f′(i) = N P (1+i)^(-N-1) − A
The equation for successive guesses in Newton’s Method is therefore
You’re thinking about leasing an $11,200 car, attracted by the advertisements of “no down payment”. The lease payment is $291 a month for four years. What is the effective interest rate you’d be paying on this lease?
Solution: A = $11,200; P = $291; N = 4×12 = 48. Car loans in your area are quoted at around 12%, so start with i = 12%/year = .01/month. Using equation 8, the equation to get each guess from the previous guess is
inew = i − [291 − 291(1+i)^-48 − 11200i] / [ 48×291(1+i)^-49 − 11200]
The computed guesses are 0.0094295242, 0.0094008156, 0.0094007411, 0.0094007411. Newton’s Method takes only four iterations to reach an answer of i = 0.0094007411 per month. Check that by substituting that in equation 2—sure enough, we get P = $291.0000000. Therefore the annual rate is 12×0.94007411 = 11.28%.
Common opinion on Usenet has been that there’s no way to solve any of the first four equations for i, and that’s true enough if you’re looking for a closed-form solution. However, David Cantrell had the idea in late summer 2004 to apply reversion of series to equation 2.
His article and the ensuing discussion are archived in the sci.math thread A Series of Interest. In an excellent example of academic integrity, after publishing his initial article Cantrell kept digging, found a 1963 article by H.E. Stelson in American Mathematical Monthly, and published a citation in a follow-up. A further follow-up gives the Mathematica code to generate any desired number of terms of the series. My equation 7 is due entirely to Cantrell’s article, but I rearranged it slightly to simplify computation.
(In a follow-up to that thread, Cantrell points out that the series converges when NP < 2A; in other words, when the total of all payments is less than double the principal. That is true for most loans, with the notable exception of home mortgages. For cases where the series does not converge, he offers a formula; see below.)
For background on reversion of series, see the Mathworld article Series Reversion.
Let’s repeat example 6 to compare this method against Newton’s Method. With P = 291, N = 48, and A =11200, I computed u = 0.005043732 and i = 0.0094015005. This is just a bit higher than the true answer of 0.009400741, which is not surprising given that the next term in the series would have a minus sign. Still, it’s probably accurate enough: substituting in equation 2 gives a payment of $291.0049718, which is $291.00 to the nearest penny.
In cases where NP ≥ 2A, David Cantrell’s series solution doesn’t converge. NP ≥ 2A means that the total paid (number of payments times payment amount) is more than double the principal amount. The most common example is a home mortgage.
He was kind enough to call my attention not only to this limitation of the series solution but to his article “Finding Interest Rate without Approximation or Root-Finding”. In that article he gave an original formula that he developed to approximate i in a wide range of situations, even where the series solution fails. I have rearranged that formula slightly and changed the variable letters for consistency with this page:
Let’s work example 6 one more time, to compare Cantrell’s approximation to the other solution methods. You’ll remember that Newton’s method got 0.94007411% a month or 11.28% a year, and the series solution got 0.94015005% a month, which is very slightly high, but still the same 11.28% a year to four significant figures.
To apply equation 9, first compute q and then substitute it with the other quantities in the approximation for :
A = $11,200, P = $291, N = 48
q = log(1+1/48) / log(2) = 0.0297473434
q is just the base-2 logarithm of (1+1/N). Odds are your calculator can’t compute logs directly in base 2, so I’ve given the formula for changing the base of logarithms. It doesn't matter whether you use logs to base 10 or base e when computing q, as long as you use the same base for both logs. For the reasons, please see Changing the Base in my “It’s the Law Too — the Laws of Logarithms”.
i ≈ [ (1 + 291/11200)^(1/q) − 1 ]^q − 1
i ≈ 0.93767347% a month or 11.25% a year
This is a bit lower than the other solutions, but still a good approximation considering that it’s much less work to obtain it.
But consider the situation in example 2. Let’s modify that example and use the principal, payment, and term to find the interest rate:
A = $225,000, P = $1619.71, N = 360
Notice that the total paid is more than double the principal amount:
NP = $583095.60 and 2A = $450,000
As predicted, Cantrell’s series solution does not converge. If we calculate equation 7 anyway, here’s what happens:
u = (1619.71×360/225000−1) / (360+1) = 0.004408687
i = 162860.6214
Equation 7 is unusable because the series fails to converge. Let’s try equation 9:
q = log(1+1/360)/log(2) = 0.0040019306
i ≈ [ (1 + 1619.71/225000)^(1/q) − 1 ]^q − 1
i ≈ 0.64645769% a month or 7.7575% a year
This is a decent approximation to the actual interest rate of 7.8000% a year. It’s certainly better than 162,800% a month!
(Newton’s method, with an initial guess of 0.4% a month, gives the answer 0.65000073 in seven iterations. This is almost exactly 7.8% a year. So you get more accuracy, at the cost of more work.)
Good news! These are just variations on the loan theme.
For instance, a savings account is just a loan from you to the bank. The difference is that payments can move in either direction: you call them deposits when they increase your balance and withdrawals when they decrease your balance. Because of the way P was originally defined, you count a withdrawal as a positive payment P and a deposit as a negative payment P.
At the end of every month, you put $100 into a mutual fund that pays 6%, compounded monthly. How much will you have at the end of five years?
Solution: P = $100, i = 6%/12 = .005, A = 0 (because you start with nothing in the account). Use equation 1:
but reverse the sign of P in the formula, because the payments are going to increase the balance and not reduce it as in a loan.
B_n = A(1+i)^N + (P/i)[(1+i)^N − 1]
B_60 = 0 + (100/.005)(1.005^60 − 1)
B_60 = $6977.00
At the end of five years you’ll have $6977.00.
An annuity is a contract, usually with an insurance company, for you to receive a fixed amount of money at stated intervals, usually monthly. This is also the same as a loan, except that the payments move only one way.
Whole life insurance works this way once you cash it in: you can take the cash value of the insurance or use it to buy an annuity. You can also purchase the annuity with a lump sum. (An insurance annuity is typically more complicated, because it factors in your life expectancy. The payments are lower than they would otherwise be, because the company guarantees to pay you until you die, or to pay your heirs for the stated period if you die early. Here we’re just concerned with a straight annuity that pays for a definite period.)
You want to purchase a 20-year annuity that will pay $500 a month. If the guaranteed interest rate is 4%, how much will the annuity cost?
Solution: P = 500, i = 4%/12 = .00333 N = 20×12 = 240. Use equation 4:
A = (P/i)[1 − (1+i)^-N]
A = (500/.00333)[1 − 1.00333^-240]
A = $82,510.93
You need a one-time payment of $82,510.93 to fund the annuity. Since the total you’ll receive is $500×240 = $120,000, you might think you’re making out like a bandit. But even though it seems that way, this is just another illustration that the value of money grows over time, so that a smaller amount now is equivalent to a larger amount spread out over time.
The above example shows an ordinary annuity, one that pays at the end of each period. An annuity that pays at the beginning of each period is called an annuity due. As you’ll see in example 11, you have to make some adjustments in the formulas when computing for an annuity due.
Suppose you have a goal, and you need to map out a plan for how to reach it. In other words, you know a future value F that you want to reach, by making N periodic payments P that earn interest i.
To find P or N in this situation, proceed almost like solving a loan. Take equation 1, which for investments is
P B_n = A(1+i)^N + --- [(1+i)^N - 1] (eq. 1) i
Now set A=0 and B_n=F (your desired future value), and solve for the future value F:
P F = 0(1+i)^N + --- [(1+i)^N - 1] i P F = --- [(1+i)^N - 1] i
and solve for P or N. Let’s start by solving for P:
P --- [(1+i)^N - 1] = F i
Note: As with all these formulas, this one assumes that you make each payment at the end of a period, so that you reach your goal on the day you make your last deposit.
You’re saving up for a down payment on a house. You expect to buy in about five years, and you’ll be looking in the $250,000 range. You need to make at least a 10% down payment, plus $2500 for closing costs. If your money fund pays 5.5%, posted and compounded every month, how much a month do you need to deposit?
Solution: N = 5×12 = 60; i = 5.5%/12 = .004583; F = 10% of $250,000 + $2500 = 27,500. Use equation 5:
P = iF / [(1+i)^N − 1]
P = .004585*27500 / [(1.004583^60 − 1]
P = 399.24
You need to put almost $400 a month into that money fund to meet your goal.
Suppose you need to meet an investment goal. You know how much a month you can save, and what interest you’ll earn. How long it will take to reach your goal?
To figure this, just solve equation 5 for N:
i F (1+i)^N - 1 = --- P i F (i+1)^N = 1 + --- P
Take log of both sides (to any base):
N * log(1+i) = log(1+iF/P)
On the same day every year, you put $2000 into stocks. If the market rises 8% a year, how many years will it take you to accumulate $40,000?
Solution: F = 40,000; P = 2000; i = .08. Use equation 6:
N = log(1+iF/P) / log(1+i)
N = log(1+.08*40000/2000)/log(1+.08)
N = log(2.6)/log(1.08)
N = 12.4
Answer: You’ll pass your goal when you make the payment at the end of the 13th year.
Have you won the lottery? Congratulations! You’ll quickly find that the actual prize is less—much less—than advertised, because of the time value of money. For instance, $1 million a year for 20 years is worth a good deal less than $20 million now (not to mention the tax bite either way). How can you decide whether to take the lump-sum buyout you’ll be offered?
You’ve won the Freedonia State Lottery. The prize is $4 million in annual payments of $200,000. The state offers you a lump sum, in lieu of payments, of $3 million now. Should you take it? If you can figure the real interest rate, you can decide whether you should take the stream of payments, or take the lump sum and invest it yourself at a better rate.
Solution: Essentially this is a $3 million loan to be repaid in 20 installments of $200,000. But you have to be careful: the formulas apply to payments at the end of every period, but in this case payments are made at the beginning of each period. (This form of payment is called an annuity due.) The way to handle a stream of payments at the start of each period is to treat the first payment as a special case, then consider the other N−1 payments at the end of each period.
If payments were at the end of each period, this would be straightforward: A = $3 million, P = $200,000, N = 20. But since the first $200,000 payment comes at the start of the first period, the 20th and last payment comes at the start of the 20th year, which is the end of the 19th year. So really you have N = 20−1 = 19. That first $200,000 payment, at the start of the first year, has to be handled separately. To keep the comparison correct, you also exclude that same $200,000 from the offered lump sum, so A = $3,000,000 − 200,000 = $2,800,000.
(A correspondent asked, “What happened to the first $200,000?” The answer is that nothing happened to it: either way you get that first $200,000 at the beginning of the stream of payments. If you take the lump sum, you also get the other $2,800,000, for a total of $3,000,000. If you take the stream of payments, you get $200,000 a year for 19 more years. So you’re not weighing $3,000,000 now against $4,000,000 in payments. Since either way you get the first $200,000 now, the difference between the plans is an extra $2,800,000 now versus 19 more payments of $200,000 later.)
So: A = $2,800,000, P = $200,000, N = 19. Let’s use Newton’s Method. Guess 10% as the initial interest rate, since the stock market sometimes does better than that. (This time the payments are annual, so you don’t divide by 12.) equation 8 becomes
i_new = [200000 − 200000 (1+i)^-19 − 2800000i] / [ 19×200000 (1+i)^-20 − 2800000]
and Newton’s method gives 0.0495777, 0.0358844, 0.0328139, 0.0325979, 0.0325968, 0.0325968. In six iterations we have about 3.26% as the interest rate implicit in the state’s offer of a lump sum. You conclude that you would do better to take the lump sum of $3 million and invest it yourself rather than to take the $4 million in payments over time. (This ignores the tax consequences of the choice. If you’re actually in this situation, consult a tax professional!)
For comparison, the series (my equation 7) finds u =0.0178571429, i = 0.0326109732, which gives P = $200,025. As you saw above, Newton’s Method took 6 iterations to find i = 0.0325967876, which gives P = $200000.
Now work the lottery example forward: suppose you invest the $3 million at 5%. How much could you get every year if you take $200,000 at the start of the first year?
Solution: Again, A = $2,800,000 and N = 19. But this time i is known (5%), and you want P. Use equation 2:
P = iA / [1 − (1+i)^-N]
P = .05 * 2,800,000 / [1 − 1.05^-19]
P = $231,686.03 a year (when i = 5%)
The formulas for finding loan payment amount, interest rate, balance, and number of payments are included in an Excel workbook available for download as a ZIP file of 11 KB.
The ZIP file contains one Excel workbook, LOAN97.XLS. In that workbook you’ll find one worksheet for each type calculation. The worksheets are all protected: use the Tab key to move through the input cells. The protection is there just to keep you from accidentally deleting a formula: there’s no password, and you can unlock any worksheet by right-clicking on the tab and selecting (In Excel 2003, use . » » .)
I created the file in Excel 2003, but saved it in Excel 97 format, and therefore it should be usable in Excel 97 or any later version. Please let me know of any problems.
Over the years I have had a request every couple of months for the solutions to more complicated scenarios: irregular payments, changing interest rates, and so on. For most of these, there’s not going to be one formula for any of the key quantities. In some scenarios, you can break the loan in pieces and analyze each piece; but in all of them, an Excel spreadsheet or similar numeric solution is available.
I’ve cobbled up a downloadable example (59 KB ZIP file). You can take that as a starting point and modify whatever you need to: make the payments irregular or on a different schedule from the interest accrual, change interest rates in the middle of the loan, and so forth.
For one specific case—frequency of payment different from the compounding period, which I understand is the norm in Canada—you can use the mortgage calculator at mw-plus.com/webcalc.php. I am grateful to Al DeRouen for drawing it to my attention.
The TI-83 and TI-84 calculators come with a package of
financial applications, including a “solver” for loan and investment
problems. To access it, press
 on most
 on the original
Timothy Mayes offers a short tutorial on solving these loan and investment problems with the TI-83 or similar.