Spreadsheet Formulas for Personal Finance

I love spreadsheets. Spreadsheet programs like Microsoft’s Excel, Apple’s Numbers and Google Sheets are the secret heroes of our civilization.

I’ve also been interested in personal finance and the FIRE community for a while—not so much in the early retirement aspect but in the financial literacy it teaches its members. I have combined my passion for both into one mega-spreadsheet that I use to track my income, expenses, savings and investments in one overview. While creating this spreadsheet I got proficient in some new formulas, which I’ll share here—and also write down for my own reference.

Annuity Functions

An annuity is a regular set of payments that accumulates into/diminishes a principal (basically a balance) that often has an associated interest on the principal. This is traditionally used for answering questions around life annuities which are products bought to guarantee an income for a certain amount of time—e.g. you want to buy an annuity that pays €250 per month for the next 10 years while the interest rate is 2%, what should its price be? These functions are also useful for calculating things before your retirement, however.

The functions for calculating with annuities are PMT, RATE, PV, NPER and FV. I will give an example for each of these and you will notice that each takes the other four as input values.

FV

FV stands for Future Value and it allows you to answer the following question: given an initial sum, an interest rate, a periodic payment and a duration, what will the balance be after the last payment? The definition is:

FV(rate, nper, pmt, pv, type)

The only argument I’ll gloss over in this post is type, which specified whether the payment is done at the beginning (1) or end (0) of the period, which matters for the interest.

You can use the FV function to answer the following question:

If I have €1000 in an investment that grows with 5% annually and I contribute €100 per month for the next 10 years, how much will accrue?

This can be calculated with the following formula:

FV(5% ÷ 12, 10 × 12, 100, 1000) = -17,175.24

Huh? Why is it negative? It’s because the formulas work with ‘inflows’ and ‘outflows’. Every month 100 flows into the ‘sum’ and at the end the whole sum flows ‘out’—therefore it’s negative. You can also flip the signs on the pmt and pv values which will make the FV result positive.

PMT

PMT means Payment and is useful for mortgage calculations and savings goals. It is defined as:

PMT(rate, nper, pv, fv, type)

For example:

I am looking to get a €200,000 mortgage to buy a house and the current interest rate is 2%. I will have to make monthly payments for the next 30 years. What will my monthly payment be?

The answer to this question is:

PMT(2% ÷ 12, 30 × 12, -200000, 0) = 739.24

Another question that can be solved with the same function is:

I have €3000 saved in an account that grows with 4% per year. I want to have €200,000 saved 10 years from now to buy a house, how much will I have to contribute per month to reach my goal?

The answer (note that pv and fv have opposite signs, because they flow in opposite directions):

PMT(4% ÷ 12, 10 × 12, 3000, -200000) = 1,327.86

RATE

RATE calculates what interest rate you need to match the other parameters. I personally haven’t found a need for this, but you could use it to figure out what investment target you need to hit to reach your goal.

RATE(nper, pmt, pv, fv, type)

As example, if you have €1000 and want to grow it to €100.000 in 20 years without making any contributions, what growth rate do you need?

RATE(20, 0, 1000, -100000) = 25.89%

Not very realistic!

NPER

NPER calculates the Number of Periods it takes to reach a Future Value. One of the fundamental blog posts in the FIRE community is The Shockingly Simple Math Behind Early Retirement—the gist of which is that the specific amounts of how much you make or save don’t matter for calculating how long until you’re financially independent, but rather the ratio between them: your savings rate. We can use NPER to make this calculation.

NPER(rate, pmt, pv, fv, type)

To recreate the table from the blogpost we use the following formula:

Investment Returns = 5%
Savings Rate = 50%
Safe Withdrawal Rate = 4%

NPER(IR, -SR, 0, (1 - SR) ÷ SWR) = 16.62 years

If we put this into a table we get the following:

Which matches the table from the original blog post.

PV

PV for Present Value makes it possible to calculate how much you need to contribute today to get a certain result in the future, or the value of something today discounted from the future.

PV(rate, nper, pmt, fv, type)

Let’s answer the following question:

I want to buy an annuity that yields €100/month for the next 10 years and the interest rate is 2%. How much will it cost?

The answer is:

PV(2% ÷ 12, 10 × 12, −100, 0) = 10,867.98

A controversial book on investing I recently read is Lifecycle Investing which argues that young people are often much too conservative in their investment strategy, because they should model their future earnings as a bond and allocate enough to equities to compensate for holding this bond, even leveraging up to 100% to get the right allocation. To calculate the present value we can use the following formula, assuming you will work for another 20 years, can save €300 per month and discount your future earnings by 4% annually:

PV(4% ÷ 12, 20 × 12, -300, 0) = -49,506.56

So we can value our future savings at about €50k in current monies and adjust our portfolio accordingly.

Conclusion

Do you have any useful spreadsheet tricks you learned recently? Please let me know, I’m always looking to expand my toolbox!

Aug 2021