We discuss how pretty much all of investing (lump sum or SIP) and borrowing (debt., EMI) can be described by a single equation! In finance workshops, people are often taught to use spreadsheet commands like PV, FV, PMT, NPER, etc., without a deeper understanding.
Consider a lump sum investment we shall label as pv (for present value). What is the future value (fv) of this investment? The well-known compounding formula gives this.
fv=pv(1+rate) nper
Here, the rate is the interest rate or the rate of return, and nper refers to the number of periods corresponding to the rate of return. We shall keep things simple here and assume the rate is the annual return and nper is in years. Other variations like monthly rates or quarterly rates are also possible.
What if I wanted to invest each year? Then, the formula is
fv= pmt[(1+rate) nper-1]/rate if the payments are made at the end of the period or
fv= (1+rate)pmt[(1+rate) nper-1]/rate if the payments are made at the start of the period
This is also known as the SIP formula. Here, pmt is the periodic payment. This can be each year, each quarter, or each month with a corresponding rate. We shall keep things simple and assume a yearly SIP. Over the long term, it matters little whether you use the monthly SIP or year SIP variants. The markets and not this formula determine the return you get!
🔥Secure your future with our Robo-advisory tool trusted by over 3,500 investors and advisors. From effortless retirement planning to funding your children’s biggest dreams, turn your financial goals into reality. 🔥
Subscribe for money management solutions via email! (Link takes you to our email sign-up form) Join 32,000+ readers in our community.
👉 New Tool Alert! NaviPlan: A Privacy-Focused Multi-asset Tracker and Goal Planner 👈
So what if you have a lump sum and periodic investments?
fv= pv(1+rate) nper + pmt[(1+rate) nper-1]/rate –> [1]
This is the combined formula (we have assumed payments are made at the end of the period).
This equation can compute fv, pv, nper, rate and pmt if the other quantities are known. Those familiar with spreadsheet formulae would immediately recognise these quantities.
The above equation represents investing. What about borrowing? I will introduce the equation first and then explain it.
balance= loanamt(1+rate) nper – emi[(1+rate) nper-1]/rate –> [2]
Let us consider the example of a home loan. Given a loan rate, how is the emi calculated? Suppose you want a loan to buy a home. Let us call the loan amount =loanamt. The bank would ask itself, suppose instead of giving this loan to you, if it invests the amount = loanamt at the home loan rate, what would be the future value fv at the end of the home loan tenure nper?
The answer is
fv=loanamt(1+rate) nper
Therfore, for the loan to make financial sense to the bank, it asks what monthly payments (emi) should be made by you at the same rate so that at the end of the loan tenure (nper), the corpus from these EMIs is equal to the fv?
In other words
fv = emi[(1+rate) nper-1]/rate
So, at the end of the loan tenure
loanamt(1+rate) nper = emi[(1+rate) nper-1]/rate
Since both of them are equal. Or we can write
0 = loanamt(1+rate) nper –emi[(1+rate) nper-1]/rate
Let us consider an example.
- loanamt = 50,00,000
- nper = 20 years = 240 months
- rate = 10%
So if the bank invests the loanamt for 20 years at 10%, it would get
loanamt(1+rate) nper =5000000*(1+(10%/12))^(20*12) = 3,66,40,368
If the bank gives it to you, the emi is 48,251. Why?
emi[(1+rate) nper-1]/rate =48251*((1+(10%/12))^(20*12)-1)/(10%/12) =3,66,40,368
So, after 20 years,
loanamt(1+rate) nper – emi[(1+rate) nper-1]/rate= zero
That is, the future values of a lump sum and SIP (= EMI) are the same at the end of the loan tenure.
What is the situation after one year?
loanamt(1+rate) nper = 5000000*(1+(10%/12))^(12*1) = 55,23,565
emi[(1+rate) nper-1]/rate = 48251*((1+(10%/12))^(12*1)-1)/(10%/12) = 6,06,302
These two numbers do not ring a bell, but
55,23,565 – 6,06,302 = 49,17,263 = home loan balance after one year of paying EMIs
Similarly
loanamt(1+rate) nper – emi[(1+rate) nper-1]/rate = home loan balance after nper years of paying EMIs
So, the full equation is
balance= loanamt(1+rate) nper – emi[(1+rate) nper-1]/rate –> [2]
This is our 2nd equation, hence the [2]. Now, compare this with the first equation.
fv= pv(1+rate) nper + pmt[(1+rate) nper-1]/rate –> [1]
We can now combine the two into one “master equation” to represent all of investing and borrowing!
fv= pv(1+rate) nper ± pmt[(1+rate) nper-1]/rate
If it is investing, use the + sign and
- fv = corpus value
- pv = lump sum investment
- rate = rate of return
- nper = duration of the investment
- pmt = periodic investment
If it is borrowing, use the – sign and
- fv = loan balance
- pv = amount borrowed
- rate = rate of borrowing
- nper = duration of the loan
- pmt = periodic payment to close the loan
All the spreadsheet formulae like PV, FV, PMT, RATE, and NPER use this master equation. I would strongly recommend students of finance and financial advisors base their results on the master equation without blindly using spreadsheet formulas.