5 Essential Google Sheets Formulas for Budgeting and Personal Finance

Spreadsheets are incredibly powerful budgeting tools.

They provide deep insights into spending habits, asset growth, and debt management. And having all your financial knowledge in one place isn’t just convenient; it increases the quality of your financial decision-making, which in the long-term, can lead to a much-improved financial life.

Google Sheets is an excellent platform for spreadsheet budgeting partly because its built-in formulas are so robust. And by knowing how to use a handful of formulas, you can analyze your finances like a pro.

1. SUMIFS: Gain deeper insights into your budget

Most people are familiar with the SUM function, which allows you to add a string of data. While the SUM function will certainly save you time, it’s pretty limited. When budgeting, it can be helpful to sum very specific data. This is where SUMIFS comes in because it will allow you to narrow the criteria for inclusion in the summation.

Here’s how the formula works:
=SUMIFS(sum range, criteria range 1, criterion 1, [criteria range 2, criterion 2…],
where the sum range is the data you wish to sum and the criteria ranges and criterion are the limiters.

For example, SUMIFS is incredibly useful for people who budget with a significant other or, at the very least, have shared expenses within their budget.

1. In your Tiller budget sheet, add a “Name” column to the left of your “Amount” column by right-clicking on the column letter and choosing “Insert 1 left.”
2. Each time you categorize a transaction, add the name of the person responsible for the transaction to the “Name” column. If it’s a shared expense, type “Shared” for the name.
3. At the end of your budgeting period, you can figure out the total amount of income and expenses per person by category by using
   =SUMIFS(Sheet4!F:F,Sheet4!D:D,”Bills”,Sheet4!E:E,”Person 1″)

If you run this formula for each combination of category and person, you can create a chart summarizing the entire month.

2. GOOGLEFINANCE: Understand your investment portfolio

One of the most complicated, yet important, aspects of your financial life is your investments. They can also happen to be one of the most difficult to track, especially if you invest through multiple platforms, such as banks, robo-investors, financial advisors, and online brokerage accounts.

Luckily, Google Sheets has a formula powered by Google Finance that will show you (almost) real-time updates on the performance of the assets within your investment portfolio in a single spreadsheet.

The basic formula is =GOOGLEFINANCE(“ticker”,”attribute”). By inputting different attributes in the formula, you can see data on current and historical performance.

For a deeper look at the power of this formula, check out this Tiller guide on how to create an investment spreadsheet.

3. XIRR: Think like a financial advisor

Regardless of your investment horizon, it’s important to keep an eye on your portfolio’s performance. Even if you’re investing for the long-term, a long period of losses or stagnant growth may indicate a need to rethink your investments.

The XIRR formula calculates your rate of return on a specific investment. If you’ve ever used the IRR formula, the XIRR formula is very similar except that it accounts for cash flows that may not occur at regular intervals.

Using XIRR, you can calculate your asset’s historical performance and use the information to make decisions about whether to buy, sell, or hold an asset.

1. Open a Google Sheet, and set up a column for dates that you checked your asset’s performance and the cash flow of the asset associated with that date. A positive cash flow means the asset increased in value, while a negative cash flow means it decreased in value.
2. Calculate the internal rate of return using the formula
   =XIRR(B2:B9,A2:A9) Based on the information in the example spreadsheet, your return is 17.71%, which is above your 5% threshold, indicating that you should hold the asset.

4. PMT, IPMT, and PPMT: Understand your debt payments

Tracking your liabilities is just as important as tracking your assets if you want to get a complete picture of your finances. When you’re considering taking on a debt, such as a mortgage, a car loan, or a student loan, it’s important to understand the repayment schedule in order to make the best decision regarding the structure of the debt.

Using the PMT, IPMT, and PPMT formulas to understand how your debt payments will be allocated against your debt can help you save a significant amount of money over time.

For example, let’s say you finance a $250,000 home over 30 years. You can figure out the yearly mortgage payment by using the formula

=PMT(rate, number of periods, present value of the loan, or in the case of this example

=PMT(5,30,25000)

Note: The PMT formula returns a negative value. This is because the amount is a negative cash flow, or a payment.

If you add a negative sign between “=” and “PMT,” your values will come out as positive numbers. If you want to calculate the monthly payment, you can use the same formula with minor adjustments. For the interest rate, divide your yearly rate by 12 to get the monthly interest rate. For the number of periods, multiply the number of years in the loan term by 12 to get the total number of months.

The PMT function shows you the total amount you pay, but it tells you nothing about how that payment is allocated. When you make a payment, part of the money goes toward the principal and part goes toward the interest. You can figure out the amount of your payment that is applied to interest and the amount applied to the principal by using the following formulas:

=IPMT(rate, period, number of periods, present value of the loan)

=PPMT(rate, period, number of periods, present value of the loan)

By aggregating PMT, IPMT, and PPMT data for the life of the loan term, you can create an amortization schedule, which shows you your payments over the life of the loan and how they are allocated.

Why does this matter? By manipulating the inputs, such as the loan term or the number of your payments, you can drastically affect the total amount you will pay.

• If you make an extra principal payment of $1,000 each year, you’ll save $2,300 in interest over the life of the loan.
• If you’re able to get a mortgage rate of 4% instead of 5%, you will save over $54,000.
• If you take out a 15-year mortgage, you’ll increase your yearly payments by $7,800, but you’ll pay $127,000 less than you will with a 30-year mortgage.

These are incredibly important considerations when discussing taking on debt. Using Google Sheets formulas, you can make an educated decision without relying solely on the advice of a loan officer who isn’t nearly as invested in your finances as you are.

5. SPARKLINE: See the big picture

The previous Google Sheets formulas were focused on getting a clearer picture of a narrow aspect of your finances. With the SPARKLINE formula, you can put all the pieces together and see a birds-eye view. Using the formula =SPARKLINE(data, {options}), you can generate visual representations of the data within your spreadsheet.

You can easily generate sparkline charts based on the outputs of some the previously discussed formulas, such as SUMIFS and GOOGLEFINANCE.

SUMIFS – Create a sparkline graph showing trends in spending by person and category.

For Person 1: =SPARKLINE(B4:D4,{“chartype”,”line”})

For Person 2: =SPARKLINE(G4:I4,{“charttype”,”bar”;“axis”,true})

Vary the data range in the first term of the formula to create a sparkline for each budget category.

GOOGLEFINANCE – Create a line or column graph showing the growth of Google stock during 2018.

The SPARKLINE formula is highly customizable depending on the type of sparkline chart you wish to use. For a complete list of options, check out Google’s SPARKLINE reference. If you really want to up your SPARKLINE game, check out Ben Collins’ comprehensive guide.

Building a solid financial foundation means understanding where your money goes as well as taking stock of your assets and liabilities. By leveraging the versatility of Google Sheets and the power of its built-in formulas, you can gain both a deep and broad understanding of your financial life in order to make more effective decisions regarding your financial future.