How to Set Up a Personal Finance Spreadsheet

Disclosure: This article is written for entertainment purposes only and should not be construed as financial or any other type of professional advice.

When presented with general concepts or rules of thumb relating to personal finance, my brain resists automatic acceptance. My look-under-the-hood tendencies rebel when I hear a statement like “it’s always better to invest rather than pay off debt” or vice versa.

To truly understand an idea (and prove or disprove its claim), I love to design a spreadsheet. Using this tool, I can break down a concept and illustrate how it works or demonstrate where it falls apart.

In this article, I’ll explain the basics of setting up personal finance spreadsheets, including how to design formulas with financial functions like PMT (payment), FV (future value), and PV (present value).

How to Get Started with Spreadsheets

Determine what spreadsheet software to use. Two readily available tools are Google Sheets and Microsoft Excel. For a while I favored Excel because I received prompts when writing formulas. The latest version of Sheets offers similar explanations.

If using Google Sheets:

• Go to Google Apps in your Google account (I access Apps from my gmail account)
• Click on Drive > New > Sheets to open a new spreadsheet (named “Untitled Spreadsheet”)
• Rename the spreadsheet “My First Spreadsheet” or whatever’s descriptive and easy to remember
• Note that I can enter values (like $100) or formulas (like =PMT) in the open “cells” that are like blank rectangles within a grid
• Make sure “all changes saved in Drive” appears before closing out the spreadsheet

If using Excel:

• Go to Excel from the main Windows screen
• Click on Blank Workbook when presented with choices that include multiple templates
• Click on File > Save As to begin the file naming and saving process
• Choose Browse and then select Documents as the place to save the spreadsheet (there are other choices but I save my spreadsheets to the Documents section)
• Enter “My First Spreadsheet” or whatever’s descriptive and easy to remember in the File name section (and write over Book1); leave Excel Workbook as the Save As type
• Note that I can enter values (like +$100) or formulas (like =PMT) in the open “cells” that are like blank rectangles within a grid
• Go to File > Save as > Save “My First Spreadsheet”and confirm the overwriting of this version to replace the older version before X-ing (or exiting) the spreadsheet

How to Use the Payment (PMT) Function

The PMT function allows me to determine the payment amount for a loan. I’ll have to enter the loan numbers (aka “arguments” or “values”) to use this function.

As an example, I’ll calculate the payment of a 4.5% fixed rate, 30-year mortgage loan in the amount of $150,000. I’ll assume a complete payoff at the end of the term (the loan balance will become $0). In addition, I’ll make the payment at the end of the year or month, not at the beginning.

Here’s what I’ll do:

• Find an open cell and type “=PMT(“
• When the PMT screen opens, enter the following values as prompted:
  • 4.5% (rate),
  • 30 (number of periods),
  • $-150000 (present value of the loan),
  • $0 (future value of the loan should be $0),
  • 0 (payments are made at the end of each period)
• Press “enter” to complete the formula and get my result, which is $9,208.73.

My result is an annual payment of a 30-year mortgage. To determine the monthly rate, I’ll design the formula like this: =PMT(4.5%/12,30*12,-150000) = $760.03. Note that this number represents the principal and interest portion of a mortgage payment.

How to Use the Future Value (FV) function

The Future Value (FV) function allows me to project the future value of an account in a specific number of years or periods. Generally, this process involves knowing how much an investment account is worth today (if anything), the dollar amount of any periodic contributions (if any), the annual rate of return (I’ll guess at this number), and the number of years from now until the future date.

As an example, I’ll determine how much an IRA will be worth in 10 years if I have $15,000 already saved, plan to contribute $5,000 annually for 10 years, and hope to earn 5% annually.

Here’s what I’ll do:

• Find an open cell and type “=FV(“
• When the FV screen opens, enter the following values as prompted:
  • 5% (interest rate),
  • 10 (number of periods),
  • -$5000 (payment amount or contribution),
  • -$15000 (present value of the account),
  • 0 (contributions are made at the end of a period, rather than before),
• Press “enter” to complete the formula and get my result, which is $87,322.88.

My result shows me the future value of my IRA, which is based on its current value plus a series of equal contributions I plan to make over a period of 10 years.

How to Use the Present Value (PV) Function

Using the present value function offers a different way for me to think about money. It makes me consider what a certain dollar amount in the future (or stream of income over a number of years) is worth right now.

For example, let’s say I want to grow an investment account to $500,000 in 30 years. If I know (or, more realistically, guess) a certain rate of return, I can calculate how much I need to invest today to reach my goal of $500,000.

Here’s what I’ll do:

• Find an open cell and type “=PV(“
• When the PV screen opens, enter the following values as prompted:
  • 8% (interest rate),
  • 30 (number of periods),
  • -$0 (this is the amount of my annual contributions; I’m planning on making a one-time deposit),
  • -$500000 (future value of the account),
  • 0 (if I made contributions, they’d be applied at the end of each period),
• Press “enter” to complete the formula and get my result, which is $49,688.67. 

So, thirty years from now, $49,688.67 will be worth $500,000 — if I can get an 8% annual return.

One application for this formula is to evaluate a pension plan offer. I can compare 1) the value of a lump sum payment with 2) a stream of payments over time — by determining their respective present values and comparing those numbers.

Limitations of Personal Finance Spreadsheets

At this point, I should mention that spreadsheets aren’t crystal balls. They don’t allow me to predict the future, even though the projections using present and future values may make me think I can.

Still, personal finance spreadsheets can help me understand what might happen if I continue on a certain trajectory, like my investments grow at a certain, guessed-at level of annual returns.

In addition, designing a spreadsheet can force me to identify and consider the variables that will influence my outcomes. Then, I can work to control these variables or take action independent of various possibilities.

For example, if I decide I need more than $87,000 in my IRA, then I might contribute more, save longer, or try to determine if I can earn a higher rate of return. If I’m already maxing out my IRA contributions, I may consider saving over a longer period of time.

Benefits of Using Personal Finance Spreadsheets

Spreadsheets enable me to construct a model of a specific assertion.

Going back to the pay-off-debt-or-invest dilemma as an example, I can gauge how a general claim applies to varying situations. I’ll define the claim, such as “it’s always best to pay off credit card debt before investing” and develop a set of assumptions to evaluate the assertion. I’ll ask:

• What interest rates and rates of return are being assumed?
• Are these numbers typical of most people’s situations?
• Do these numbers represent my situation or my past experience?
• What other factors should I consider in addition to these numbers?

After building a template — with formulas depicting how the numbers work — I can learn whether general assertions hold true and under what conditions. More importantly, I can determine not only how certain concepts may affect me but also decide what’s best for me in specific situations.

Understanding how to set up a personal finance spreadsheet and use financial functions can help inform decisions.

Do you like using spreadsheets to make personal finance decisions? Would you rather design a spreadsheet yourself or use an online calculator? What types of spreadsheets have (or could) help you?