In the early stages of my personal finance journey, I learnt how to build and use basic personal finance tools. I had budget templates, income/expense trackers, mortgage calculators, and so on.

Over time, I realised that I wanted to forecast what would happen in the future. How long would it take to pay off my mortgage if I paid more than the minimum monthly payments? If I invested this much money every month, how much would I have at the end of 10 years?

There were many tools/templates out there, and I built my own mortgage repayment calculator, compound interest calculator, and other tools. However, there was one problem that none of the tools, or anything I found online, could solve.

I wanted a tool that integrated everything from expenses to investment rates, extra mortgage repayments, etc., to predict what my financial situation would be like at any point in the future, across many different scenarios. What if investment returns for index funds become 5%? What if the cost of university increases by 10% every year?

With isolated tools, seeing how the output of one tool affected another tool was very difficult. For example, developing an understanding of how my retirement fund would be affected 20 years from now if I saved up money for an expensive private school, or sent my kids to public school instead.

The typical tools and calculators all worked in isolation, but life is not like that. Luckily, I’ve gained some skills during my career that would come in handy in solving this problem and developing my own tools. Using my past experience in banking and consulting. I’ve developed my own personal financial model to forecast my finances into the future, across many scenarios.

If you have a financial advisor, ideally he/she has their own model that they use to analyse your situation and help you make sound financial decisions.

That was one of the main reasons I hired a financial advisor; To compare the results of my own model with another independent model, to make sure I wasn’t missing anything.

And I’m going to show you how to create your own model. It’s an extremely powerful tool, way better than the typical budget or expense tracker.

What you will learn in this post about personal financial models

This post is a continuation of my Developing your Financial Plan Series, and a really crucial post as I think it covers new ground for most people especially if you don’t have a finance or consulting background. It is part of the next-level wealth meta.

In this post I’ll cover:

• What is a personal financial model
• How it helps your personal finance journey
• How to develop a model
• Ideas to customise and improve on the sample model to suit your needs

What is a personal financial model?

A model is a tool to calculate or estimate potential outcomes based on certain inputs and/or assumptions.

By definition, this can be as simple as a tool that calculates how much money you’ll have at the end of a specified number of years if you invest a monthly into, say a term deposit.

In terms of personal finance, this would be a tool that helps forecast what is your future net worth over a series of time, based on your specific circumstances of:

• Current and future income potential
• Current and future expenses (these are typically your SMART goals)
• Potential investment gains

Curious to see what it might look like? The screenshots below are taken from a simplified model which I created. It is broken down into:

1. Dashboard (and inputs)

A dashboard page showing a visual representation of projected future net worth, with the ability to toggle inputs like income, expenses and return rates.

2. Cashflow / Budget projections

Behind the scenes, the model calculates the cashflow, using income, expenses, short-term and longer-term savings targets based on SMART financial goals. Think of this as your future budget and savings that evolves over time as circumstances change.

3. Net Worth projections

From those savings, the net worth section of a model would project the potential value of accounts and investments. It would also be based on the amounts saved, rates of return and when the funds are used/withdrawn.

Although this sample model is somewhat simplified, it’s a useful starting point for someone new to the concept of financial modelling to understand how beneficial it can be.

Why use a personal financial model?

Having a personal finance model is much more powerful than the typical budgeting template or FIRE calculator that is commonly found.

As you can see from the pictures above, having a personal financial model gives you greater insight into your future with greater accuracy, by:

• Being able to understand granular changes to your future cash flow and net worth over time
• Allows you to adjust your plan for future changes in income, expense, investment returns, and hence, changes in net worth
• Enabling you to consolidate all your SMART financial goals to compare with your income and expenses to see if it is achievable, and adjust your targets/spending to help you find the right balance

With a tool like this, you’re able to analyse different scenarios, for example, what would happen to my net worth in 20 years time if I buy a property in 3 years time, versus only buying a property in 8 years time and investing the money beforehand.

How does a personal financial model work (behind the scenes)?

Let me start by outlining a few basic principles and common practices in modelling:

• The first sheet is a dashboard, with key inputs/assumptions that you can adjust. Bigger and more complex models may have their own dedicated worksheet for inputs and assumptions, but since this is a simple model, I’ve merged it with the dashboard
• Each row is a set of data and calculations, grouped into sections, for example, expenses and how inflation rates
• Each column going from left to right is ONE time period. It could be a month, a quarter, a year… any time period per column as long as it’s consistent. The smaller the time period, the more detailed your calculations will get
• Ideally, models should be designed in a manner that minimises the risk of errors when adjustments or changes to inputs occur. This is done by having all calculations when using similar inputs (e.g. inflation rate) refer to only one cell as input (in this case, the cells in the Dashboard worksheet).
  • There can be exceptions as you can see in my sample, but it should only be when big changes in the input can occur in different time periods

Now I could write a wall of text about how it works, but I think the few pictures below for each of the sections of the model paints a thousand words:

1. Dashboard (and inputs)

2. Cashflow / Budget projections

3. Net Worth projections

Two important caveats

• Financial models are never 100% accurate. No one can predict the future. Models only help to provide a level of confidence in decision-making. The quality and “accuracy” of the projections are based on the quality of the inputs. Rubbish in and rubbish out.
• Personal circumstances change. As a result, like your budget, you need to adjust and update over time as new information comes in, such as a new promotion leading to an increase in salary. It is fun though to keep track of historical model outputs you’ve built and compare it to how you’ve actually performed.

Bonus: Downloadable Personal Finance Model

Click here to download the sample model. It’s totally free, as a part of my mission to give back to others and help everyone learn.

I hope you find it useful not only to learn about financial models but also how to create your own in Excel.

Frequently Asked Questions

What are things I can do to expand or improve on this simplistic model?

Non-exhaustive options below:

• Include a detailed budget section – Create an additional worksheet, and start listing out your budget (monthly and lumpy expenses, line by line). Sum it up, and use the sums as reference inputs for the Projection sheet expenses section. Then as your budget changes over time, update the sheet accordingly
• Expand the dashboard – Everyone loves dashboards. Track savings rates, NW/FIRE targets, mortgage balance, and so on
• Use gross income – To account for tax rates, changes to pension fund contribution amounts, etc. which are typically what is taken out before your take-home pay
• Create scenario toggling functionality – Want to switch through different inputs quickly? For example, between 8%, 10% or 12% investment return rates? What about working for 5 more years vs 10 more years? Scenario toggling makes the model exponentially more powerful to make better decisions

Personal finance models seem too complex and difficult!

Models can be as simple or as complex as you want them to be. It’s up to you. It’s really part science part art. There is a fine balance in ensuring the “big rocks” are accounted for, and the more granular inputs may not be worth the complexity/effort if it doesn’t really change the final outputs by much.

Keep it simple at first, then as you learn more, iterate and improve your model to suit your needs. My model took years to refine. At one stage it got overly complex, and I decided to simplify it to a level that I felt comfortable with. (This sample is not the model I use personally)

Can I modify your sample model to suit my needs?

Sure! It’s not password-protected. Just unprotect the sheets. I’m giving this away. For free. Although it’s always nice to give me some credit if you continue to share it with others.

Conclusion

I hope this is eye-opening to you. Financial models are a key tool that financial advisors use (or should, check with your advisor, he might have his own Excel or a proprietary tool) to analyse and project your future financial situation.

What I’ve covered in this post is the science of a financial model. There is an art to it though, as you might have noticed, you might get stuck trying to figure out what input numbers to use (e.g. what inflation rate or investment rate of return to use).

I’ll cover the art of understanding and using the “right” inputs and assumption numbers in my next post.