As an Amazon Associate I earn from qualifying purchases.
When you get deep enough into the financial independence community, you are going to want to start building your own spreadsheets. Don’t get me wrong, the spreadsheets available out there for free are great. But sometimes you want something quick or the pre-built ones are just missing something you need. I have three essential personal finance formulas and how to use them.
Finance formulas for personal finance
Time to dust off those Excel (or Google Sheets) skills or pick up some new ones. Spreadsheets, for people not in accounting or finance, might seem like a complicated mess of squares involving way too much math. If that is the case then the formulas must seem even more mysterious. I promise after reading this you’ll be able to use these simple personal finance formulas in Excel like a pro.
Make the machine do the math
You might be surprised to know this doesn’t involve a lot of math. That is the beauty of using Excel (or GSheets): the computer does all of the math for you and probably makes fewer mistakes. The other big advantage for the financial independence community is the finance formulas make it super easy to change inputs over and over to see the results. Which is one of my favorite things to do because I’m a huge math nerd.
Have you ever been punching numbers into a calculator to see how your savings might grow and want to change something or see multiple versions? Maybe you want to see how changing the time frame affects the amount. Perhaps you want to see if a higher or lower interest rate really makes that much of a difference. Maybe you want to see how adding just $50 more a month in savings increases your savings in 10 years.
With great power comes great responsibility
Run 1 calculation or 10,000 with Excel. That is the magic. It is all the same to the computer. If you set up a good input section you can easily change the parameters over and over and over to see every conceivable scenario play out instantly. You could even copy the entire sheet to run side-by-side comparisons within seconds.
And we just stumbled across a dangerous path you need to avoid. The path of analysis paralysis. When you have a ton of data and the ability to analyze it a thousand different ways you can get stuck in a loop of just messing with the numbers instead of taking action. Go ahead and build out all of the scenarios in your head but stick to realistic ones and limit them to a couple for each project. Imagining a 15% stock market return on your portfolio to see it grow rapidly is fun but might not be a great use of your time.
Get to the finance formulas already
Ok, geez. The three finance formulas I highlight below are by no means a comprehensive list of the amazing formulas available to you in Microsoft Excel or Google Sheets. We don’t have time for that nor do many of them apply to personal finance calculations as directly. These are the formulas I and your other favorite personal finance bloggers probably use the most. You’ve seen them indirectly in the blog posts and have probably wondered how they work. Here you go.
Quick notes on formula usage
All spreadsheet formulas start with the equal sign [=] to tell the program you want this to be evaluated (as opposed to just text of that word). The formula components are placed inside a set of parentheses and separated by commas. The order of the components very much does matter so make sure to double check that if you encounter errors.
For example, if the formula is =FORMULA( a, b, c ) that means the formula you are using requires three inputs a, b, and c and should be included in that order separated by commas. It is less common but sometimes inputs are optional and the program will usually note that by putting the input names in brackets. Like I said, it isn’t common and I usually fill in the optional fields anyway to be sure it operates the way I intend.
Finance Formulas: IF
- = IF (logical statement, value if true, value if false )
- Logical statement → this is a calculation that results in TRUE or FALSE only
- Value if TRUE → What do you want to show if the logical statement is TRUE?
- Value if FALSE → What do you want to show if the logical statement is FALSE?
Ah, the IF statement. The king of the formulas, the basis for a lot of coding, and the formula I use the most. This is the swiss army knife of spreadsheet formulas because it can (and probably should) be used on any project.
This formula has only three inputs: logical_statement, value_if_true, and value_if_false. What that means is you are going to give it something to check that results in true or false and what to display depending on the results. It makes simple decisions for you based on the inputs to keep your spreadsheet going.
Example time: IF
Let’s look at a quick example. You have a list of your checking account transactions you want to quickly organize when reviewing your budget, for instance. Your checking account is going to have money coming in (paychecks, deposits, etc) and money going out (bills, spending, etc) all jumbled together. You only care about the expenses right now and want a new column to categorize them. You could use a formula like =IF([amount column]<0, “Expense”, “Income”) and that would label all money going out as an expense and all money coming in as income.
Where to use IF
There are a lot of practical uses for a simple formula like IF. Do you want a cell to change its displayed result when a loan amount hits zero so it doesn’t keep going? Perhaps you want to be able to shift between annual and monthly rates and have the downstream formulas adjust accordingly instead of breaking? Do you want a fun cell to say SUCCESS when you reach your FI number? Do you want to only show amounts on weekdays in a list of dates? The IF statement can do that.
Finance Formulas: SUMPRODUCT
- =SUMPRODUCT( array1, array2 )
- Array1 → a first group of continuous cells
- Array2 → a second group of continuous cells
I won’t lie to you, this one isn’t used all the time but it is still very useful to people in the personal finance world. This finance formula is the fastest and easiest way to calculate the weighted average interest rate for a group of accounts. This is the one to use if you need to know the average rate you are paying across all of your loans, the average operating expense ratio you are spending on your investment accounts, or the overall investment performance percent change.
I hope by now you know you can’t just take a straight average of the loan rates, operating expense ratios, or YTD investment gains to get those answers. Every account has a different rate and balance that have to be considered to get the proper result. If the majority of your investment balances are earning 1% and one small account is earning 15%, the weighted average is going to be much closer to 1% than the straight average of 8% of course.
Example time: SUMPRODUCT
This formula has only two inputs: array 1 and array 2 which must be of the same size or shape. An array is a fancy term for a group of continuous cells in a row or column. The formula functionally multiplies the two arrays together row-by-row or column-by-column and adds up the many results to produce an output. For our purposes in personal finance, array 1 will almost always be the Percent of Total field and array 2 will be the rates.
|1||Loan Balance||% of Total||Interest Rate|
|2||$ 50||10%||18 %|
|3||$ 100||20%||5 %|
|4||$ 350||70%||3 %|
|5||$ 500||=SUMPRODUCT(C2:C4, D2:D4)|
In the example above the straight average of the rates would be 9% but the weighted average is actually 5%. The SUMPRODUCT formula takes into account that the bulk of your portfolio is at 3% and weights it accordingly in the average. Note: I left the column and row names in view so you can see the formula references.
Where to use SUMPRODUCT
Use this formula anytime you need a weighted average result without needing to add a bunch of calculation columns that look ugly. This is perfect for calculating your 401k overall expense ratio to see if you need to make some changes. I was able to add $15,000 to my retirement fund in 15 minutes after determining I was paying too much in fees with this formula. Fifteen grand seems like a decent payoff for learning a new spreadsheet formula.
Finance Formulas: FV (Future Value)
- =FV( rate, number_of_periods, payment_amount, present_value, 0 )
- rate → market rate per time period
- number_of_periods → number of time periods to calculate
- payment_amount → amount to add each time period (zero if none)
- present_value → current value of your savings/investments
- 0 → this field just needs a zero to denote interest is added at the end
Have you ever visited bankrate.com to use their Simple Savings Calculator to figure out how much interest your high yield savings account will earn you? I’m sure we all have at one point or another. The FV formula is basically this calculator for spreadsheets and the secret weapon for calculating your savings growth. Or fantasizing about it… I won’t judge.
Those calculators online are great but sometimes you need more flexibility. You might want to see how 3 different accounts grow over the next ten years when they have different starting balances, interest rates, and contributions. I mean, you could open 3 different browser pages for the calculator and do them separately and add them together. You might want to save those results though. *Spreadsheets have entered the chat* The FV formula can do that for you on one page for numerous accounts and save it to tinker with later. Because we will tinker.
Example time: FV formula
This formula has five inputs: rate, number_of_periods, payment_amount, present_value, and a zero. It sounds like a lot but they are pretty easy to handle. The rate is the interest you expect to earn (or rate of growth) for the account input as a decimal number like 0.08 for 8%. The number of periods is how far into the future to calculate input as a whole number like 36 for 3 years in months. The payment amount is what you are adding to the savings account each period, if any amount, input as a number like 100 for adding $100 a month. The present value is what your account balance is at the start input as a number like $1000 for a thousand dollar balance. Finally, we input a zero which tells the computer to add the interest gains at the end of the month.
I need to stop here and note that it is VERY IMPORTANT that the rate, number of periods, and payment amounts are all using the same time period. If you are using months as your time period, then your rate needs to be divided by 12 (to get the monthly rate equivalent). Similarly the number of periods needs to be in months (6 months instead of 3 years) and the payment amount needs to be for that period ($100 for monthly contribution instead of $1,200 annual). A time period mismatch here will definitely give you the wrong results and sometimes it is hard to realize that was the mistake when troubleshooting.
See =FV in action
|1||Growth Rate||8 %|
|2||# of Periods (years)||10|
|5||Future Value||= -FV(C1/12, C2*12, C3, C4, 0 )|
The table above shows a spreadsheet set up for seeing how much a $1,000 investment account grows over 10 years at 8% return when you contribute $100 a month. Notice how I divided the rate by 12 to make sure we used a monthly rate. I also multiplied the 10 years by 12 to convert that input to months as well. The contributions were already in monthly amounts so we were good to go there. Also, I placed a negative sign at the start of the formula because the outputs are always negative. There is a long, boring reason why the outflows of this financial calculation are negative for mathematical reasons but skip that and just put the minus sign in front of the formula.
When to use the Future Value (FV) formula
The result of that investment example above is $20,514 by the way. This formula allows you to change around the inputs and really see the power of compounding interest over time. Depending on how you set up your worksheet, it also allows for more flexible planning as well. You could set it up to have contributions of $100 a month for the next 3 years then bump up to $400 a month when you finally pay off that car. Maybe you want to have the contribution go up a little each year as you get that annual pay raise.
The Future Value formula can really show you the power of compound interest with your own numbers. It can also show you the devastating effect of operating expenses and investment advisor fees if you want. I used several FV formulas side by side to show how an investment advisor and mutual funds nearly cost my son $10,000 in lost college savings. Those 1-2% fees seem so small until you magnify them out over the next 10+ years.
Spreadsheets only seem daunting at first. Think of them as a place to collect your data and have a machine do all of the calculations you need. Start with these three formulas and build out some simple worksheets for your financial life. Whenever you come across a situation where simple addition, subtraction, multiplication, or division don’t seem to be cutting it, see if the spreadsheet has a built-in finance formula to do it for you. If you can’t find it easily, feel free to ask me here in the comments.
Do you have a favorite spreadsheet formula for Microsoft Excel or Google Sheets? Do you have finance formulas you want a better explanation on from me? Add them in the comments below and I can continue to share quick how-to posts on using spreadsheets for personal finance Excel.
Personal finance formulas in Microsoft Excel or Google Sheets are some of the most powerful tools on the path to financial independence. Simple calculations are fine but eventually you want to graduate to more complex calculations. I shared how to use IF, SUMPRODUCT, and FV formulas above to build killer spreadsheets.
Amazon and the Amazon logo are trademarks of Amazon.com, Inc, or its affiliates.
Leave a Reply