Understanding financial functions in Excel/Google Sheets

A visual understanding on how FV, PV, RATE, PMT NPER etc are related.

Published: Oct 2023

\gdef\pv{\textcolor{BEA1A5}{\small\it{PV}}} \gdef\fv{\textcolor{BE4BDB}{\small\it{FV}}} \gdef\pmt{\textcolor{008FD3}{\small\it{PMT}}} \gdef\rate{\textcolor{32B67A}{\small\it{RATE}}} \gdef\nper{\textcolor{FF4552}{\small\it{NPER}}}

You might have used functions like FV, PV, XIRR in excel/google sheetsIn rest of the article, I will use excel to mean Excel, Google Sheets, OpenOffice etc. 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. $\pv, \rate, \nper, \pmt, \fv$ are related. Given any four, the fifth value can be derivedIt's almost as if all these functions are different sides of the same coin, or more concretely, different ways of using the same equation.. 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 (no risk, no volatility, no taxes). 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, end_or_begining or 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 moneyAssumes your withdrawl rate would be same after retirment. And rate is real interest rate. Is calculating with real interest correct? It seems so. Calculating with real interest instead of nominal, seems to be a pretty good approximation. See this Google Sheet..

Now, to test your understanding, try calculating CAGR with one of these functions?

If you are in India, here is another interesting one. Calculate the effective annual rate of return for a ULIP that you have come across.

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 about NPER, PMT? Given the payments, we implicitly have the payments and the periods!.

IRR(cashflow_amounts)
NPV(rate, cashflows_amounts)

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 $\rate, \nper, \pmt, \pv, \fv$ .

Underlying calculation

For constant period and payments, the underlying equation is relatively straighforward.

\fv\space+\space\pv\times(1+\rate)^{\nper}\space+\space\pmt \times \frac{(1+\rate)^{\nper} - 1}{\rate} = 0

First consider the PV.

\fv\space+\space\htmlClass{bg-gray-200 py-1}{ \pv\times(1+\rate)^{\nper} }\space+\space\pmt \times \frac{(1+\rate)^{\nper} - 1}{\rate} = 0

The first year, $\pv$ grows to

$\pv * (1+\rate)$

Next year, it grows to

$\pv * (1+ \rate) * (1 + \rate) \implies \pv * (1+\rate)^2$ .

After $\nper$ years, the PV grows to

$\pv * (1+\rate)^{\nper}$

This is the end value of $\pv$ . Now let's consider $\pmt$ .

\fv\space+\space\pv\times(1+\rate)^{\nper}\space+\space\htmlClass{bg-gray-200 py-3}{\pmt \times \frac{(1+\rate)^{\nper} - 1}{\rate}} = 0

The first $\pmt$ payment has $\nper-1$ years to grow. So, by the end of $\nper$ period, it would be

$\pmt * (1+\rate)^{(\nper-1)}$

Second $\pmt$ has $\nper-2$ years to grow. So, at the end of $\nper$ period, it will be $\pmt * (1+\rate)^{(\nper-2)}$

And so on. If we add up value of all payments ( $\pmt$ ) at the end of $\nper$ , it would be

\pmt \times (1+\rate)^{\nper - 1} + \pmt \times (1+\rate)^{\nper - 2} + \ldots + \pmt \times (1+\rate) + \pmt

\implies\pmt \times \Big( (1+\rate)^{\nper - 1} + (1+\rate)^{\nper - 2} + \ldots + (1+\rate) + 1\Big)

Squint at it a little and you will see the part in the big bracket being similar to.

x^{n-1} + x^{n-2} + \ldots + x + 1

Where $x = (1 + \rate)$ and $n = \nper$ .

To simplify this equation, let's assume

S = x^{n-1} + x^{n-2} + \ldots + x + 1

Multiply both sides by $x$ .

xS = x^{n} + x^{n-1} + \ldots + x^2 + x

Substracting the $S$ from $xS$ , we get.

(x - 1)\times S = x^{n} - 1

\implies S = \frac{x^{n} - 1}{x-1}

Substituting back values of $x = 1+\rate$ and $n = \nper$ , we get.

\pmt \times \frac{(1+\rate)^{\nper} - 1}{\rate}

That gives us contribution from $\pmt$ . Now let's look at the whole equation again.

\fv\space+\space\pv\times(1+\rate)^{\nper}\space+\space\pmt \times \frac{(1+\rate)^{\nper} - 1}{\rate} = 0

Why does the sum equal to $0$ ?. 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.

Conclusion

This picture and a mental model around what it means, should help you remember many of the financial functions in most excel implementations.

Acknowledgement

Thanks to Anantha Kumaran for feedback.

...