Understanding financial functions in Excel/Google Sheets
A visual understanding on how FV, PV, RATE, PMT NPER etc are related.
You might have used functions like FV, PV, XIRR in excel/google sheets. In my case, whenever I needed to model some financial calculations, I would struggle with search junk, find out some function, lookup the parameter order, try it out etc. More than that, I couldn't even think of functions like PMT, RATE etc. Turns out, understanding the underlying model makes it much easier to remember and use these functions. are related. Given any four, the fifth value can be derived. Moreover, the underlying model could be quite useful in common man's financial jouney, be it loans or retirement. And it's not complicated. Here is a visual representation of the underlying model.
Rest of the article explains this visual and adds some more details.
Cashflows - a visual representation
These functions work on cashflows. What is cashflow? Basically money moving in and out of an asset or a liability. Assets (investments, etc) and liability (loans, etc) are handled the same way. It's just a matter of passing in different numbers (and their signs, +ve/-ve) to the functions.
Let's start with a concrete example. Say you want to plan for an expense of $100,000, in 5 years. And you have access to an investment vehicle which gives you a return of 10% per annum. You have $3,000 to start with. You want to figure out how much you should invest each year. Here is how the question looks like, in the visual model. You want to figure out PMT.
To find the value, you would use PMT function in excel: PMT(0.10, 5, 3000, -100000)
Now, let's remove the specifics. First, it doesn't matter if the period 5 was years, months, quarters or something else. As long as the payments are done each period. And the interest rate is per period. So, we abstract away 5 years to 5 periods and rename it NPER (presumably for N PERiods). Payment in each period is PMT. Rename the money we expect in the end to FV (Future Value). Rename 10% interest to RATE. And the $3,000 initial money to PV (Present Value). Here is the same excel calculation with new names: PMT(RATE, NPER, PV, FV).
Functions on cashflow
Now, looking at this general visual representation of cashflow, we could wonder, what if we want to know RATE, given NPER, PMT, PV, FV. Or some other combination. And it does work. Here is the list of these related functions available in most excel implementations.
FV ( RATE, NPER, PMT, [PV], [when_due] )PMT ( RATE, NPER, PV, [FV], [when_due] )PV ( RATE, NPER, PMT, [FV], [when_due] )NPER ( RATE, PMT, PV, [FV], [when_due] )RATE ( NPER, PMT, PV, [FV], [when_due] )
Isn't it nice that even the parameters have a certain order? You can see the parameter order across functions, below. Pick the function you want to caculate, move it forward and rest is the order of the parameters to that function. In general, you start with some money (could be +ve, -ve or 0), put some money in (or take out), every period, for NPER periods. And in the end, you take out (or owe) some money. The parameters in
 are optional and if not give, default to 0.
[RATE, NPER, PMT, PV, FV]
The last parameter,
when_due, is a flag for when the payments are made. 0 for end, 1 for begining of the period. See the visual model.
And here is an interesting one use of this model NPER(interest, -savings_rate, 0, (1-savings_rate) ÷ withdrawal_rate), copied from Bouke's blog. Given a interest rate for all future and your savings rate, it calculates the number of years till you have retirement money.
Now, to test your understanding, try calculating CAGR with one of these functions?
Till now we dealt with cases where PMT (the money you pay/receive every period) and the period are same for the cashflows. In other words, you pay/receive the same amount every period. What if you have cashflows with different amount of payments. What if you have different cashflows and uneven periods (when you made the payments).
Functions on changing cashflows
First, let's consider the case where periods are same, with only the payments per period could be different from one period to another. The function IRR gives the rate of return. It's a single rate, which accounts for all the cashflows. NPV is the other function, to get the present value of the cashflows. There is no NFV, not sure why. .
What if both the payments and when you made them, can vary? E.g. ocassional investments made to a mutual fund. XIRR is a way to understand returns for these cashflows. And XNPV for present value. Again, no XNFV, although it seems to be less useful.
XIRR(cashflow_amounts, chasflow_dates)XNPV(rate, cashflow_amounts, cashflow_dates)
These calculations for uneven periods and different payments, are iterative. Solving them involves guessing a value, and then iteratively improving on the guess. Unlike the ones for .
For constant period and payments, the underlying equation is relatively straighforward.
First consider the PV.
The first year, grows to
Next year, it grows to
After years, the PV grows to
This is the end value of . Now let's consider .
The first payment has years to grow. So, by the end of period, it would be
Second has years to grow. So, at the end of period, it will be
And so on. If we add up value of all payments () at the end of , it would be
Squint at it a little and you will see the part in the big bracket being similar to.
Where and .
To simplify this equation, let's assume
Multiply both sides by .
Substracting the from , we get.
Substituting back values of and , we get.
That gives us contribution from . Now let's look at the whole equation again.
Why does the sum equal to ?. My understanding is that it's a convention. You can think of it as putting some money in an investment, and after some time, taking everything out. The amount in the investment should be 0.
IRR, NPV, XIRR, XNPV calculations
OpenOffice Document Format documents functions on irregular cashflows in regular periods (NPV, IRR), and irregular cashflows in irregular periods (XNPV, XIRR). To understand calculations for these, it helps to understand NPV first. I might add the explanation here, in future. But till then, you could try investopedia explanation. IRR is the rate at which NPV is 0. And similarly, XIRR is the rate at which XNPV is 0.
This picture and a mental model around what it means, should help you remember many of the financial functions in most excel implementations.
Thanks to Anantha Kumaran for feedback.