Savings Rate Formula in Excel: Pick One Definition and Track It Honestly

Your savings rate is the share of your income that you keep. It is the most useful single number in a personal budget, because it moves with both sides of the ledger. If you earn more it goes up. If you spend less it also goes up.

The formula looks simple: savings divided by income. The trouble is that "savings" and "income" can each mean several things. Pick different versions and one household can report a rate anywhere from 20% to 34%. None of those numbers is wrong. They just answer different questions, and they cannot be compared with each other.

This guide builds each version in Excel from one worked example. Then it shows how to track the rate month by month without the averaging mistake that inflates or deflates most trackers.

The worked example

Put these inputs in their own cells:

| Cell | Input | Example (monthly) | |---|---|---| | B2 | Gross pay | 10,000 | | B3 | Pre-tax 401(k) contribution | 600 | | B4 | Employer match | 300 | | B5 | Take-home pay (after tax and the 401(k)) | 7,800 | | B6 | Spending, everything that leaves checking | 6,200 | | B7 | Mortgage principal inside that spending | 500 |

This is a household earning $120,000 a year. They put 6% into a 401(k) and get a 3% match. Taxes and payroll deductions take $1,600 a month, which leaves $7,800 of take-home pay. They spend $6,200 of it, and that includes a mortgage payment with $500 of principal in it.

Cash saved from take-home is one subtraction:

` B8: =B5-B6 -> 1,600 `

Version 1: cash saved over take-home pay

` =B8/B5 -> 20.51% `

This is the version most budget apps show. It only sees money that hit checking. It is easy to measure, but it leaves out the 401(k) and the match. Both of those are real savings. Someone who switches from saving in a brokerage account to saving through payroll would see this rate fall even though nothing changed.

Version 2: add the pre-tax contribution back

If you count the 401(k) contribution as savings, count it as income too. Otherwise the numerator grows and the denominator does not, and the rate is overstated.

` =(B8+B3)/(B5+B3) -> 26.19% `

That is 2,200 over 8,400. The 401(k) money was never in your checking account, but it was still your income and you still kept it.

Version 3: add the employer match

The match is compensation you would lose if you left. Most people who track this seriously include it, on both sides again.

` =(B8+B3+B4)/(B5+B3+B4) -> 28.74% `

That is 2,500 over 8,700. The denominator here equals take-home pay plus everything that went to savings before it reached you. It is the income you actually had control over after tax.

Version 4: the gross pay basis

Some people divide by gross pay instead:

` =(B8+B3+B4)/(B2+B4) -> 24.27% `

That is 2,500 over 10,300. This mixes in your tax bill, which changes with where you live and how you file, so the number tells you more about taxes than about habits. It is fine if you always use it. But it should not be compared with someone else's after-tax rate.

Version 5: count mortgage principal as savings

Principal paid down on a mortgage raises your net worth the same way a deposit does. Some trackers move it out of spending and into savings:

` =(B8+B3+B4+B7)/(B5+B3+B4) -> 34.48% `

That is 3,000 over 8,700. It is a defensible view of wealth building. It is also the definition most likely to flatter you, because the principal share of a mortgage payment grows every year even if you do nothing.

The same household, five answers

| Version | Numerator | Denominator | Rate | |---|---|---|---| | 1. Cash over take-home | 1,600 | 7,800 | 20.51% | | 2. Plus 401(k) | 2,200 | 8,400 | 26.19% | | 3. Plus match | 2,500 | 8,700 | 28.74% | | 4. Gross basis | 2,500 | 10,300 | 24.27% | | 5. Plus principal | 3,000 | 8,700 | 34.48% |

A 14-point spread from one paycheck. The spread is the reason to write your definition down in the workbook before you start tracking.

A good default is Version 3. It counts every dollar you kept, and it excludes taxes you cannot control. It also has a useful property: its denominator minus its numerator is exactly your spending. 8,700 minus 2,500 is 6,200, the same as B6. So Version 3 is the only one on the list that connects your savings rate directly to the life you are paying for. If you want to credit principal, track it as a second line rather than blending it in.

Why the rate matters more than the dollar amount

Each month this household saves $2,500 and spends $6,200. So each month of saving covers about 0.40 months of spending. At the Version 1 rate it would look like 0.26 months.

That ratio is the reason the savings rate matters more than the dollar amount. A raise that goes straight into spending leaves the ratio flat. A cut in spending moves it twice. You save more and you need less. This is arithmetic, not a forecast of any investment return.

Tracking it by month: sum first, then divide

Here is where most spreadsheets go wrong. Suppose you track Version 1 for three months:

| Month | Income | Saved | Monthly rate | |---|---|---|---| | Jan | 7,800 | 1,600 | 20.51% | | Feb | 7,800 | 400 | 5.13% | | Mar | 11,800 | 5,200 | 44.07% |

February had a car repair. March had a $4,000 bonus. The obvious year-to-date formula averages the monthly rates:

` =AVERAGE(D2:D4) -> 23.24% `

That is wrong. It gives February's small income the same weight as March's big one. The right answer divides total saved by total income:

` =SUM(C2:C4)/SUM(B2:B4) -> 26.28% `

That is 7,200 over 27,400. The gap here is 3 points in three months, and it grows in years with bonuses, commissions, or irregular freelance income. An average of ratios is almost never the ratio you want.

The same rule applies to a trailing 12-month rate, which is the best single number for judging a year. In row 13 of a monthly table:

` =SUM(C2:C13)/SUM(B2:B13) `

For a rolling window as new months are added, use OFFSET or INDEX so the window stays 12 rows long:

` =SUM(INDEX(C:C,ROW()-11):C13)/SUM(INDEX(B:B,ROW()-11):B13) `

Pulling the inputs from a transaction log

If you log each transaction with a date, an amount, and a category, SUMIFS fills the monthly table for you. With dates in column A, categories in column B, and amounts in column C of a sheet called Transactions:

` Income: =SUMIFS(Transactions!C:C, Transactions!B:B, "Income", Transactions!A:A, ">="&E2, Transactions!A:A, "<"&EDATE(E2,1)) Spent: =SUMIFS(Transactions!C:C, Transactions!B:B, "<>Income", Transactions!A:A, ">="&E2, Transactions!A:A, "<"&EDATE(E2,1)) `

E2 holds the first day of the month. The EDATE bound catches every date in that month without hard-coding 28, 30, or 31 days. Add the payroll items (the 401(k) and the match) as their own monthly rows, because they never pass through checking and your log will not see them.

Three checks before you trust the number

  1. Transfers are not spending. A move from checking to savings is not an expense. If your log counts it as one, your rate drops for no reason. Give transfers their own category and leave them out of the Spent formula.
  2. Refunds net against the category. A $200 return should reduce spending, not add to income.
  3. Annual bills distort single months. Insurance or property tax paid once a year will make one month look terrible. Judge the trailing 12 months, not the worst month.

Summary

For education and planning only, not financial advice.

If you would rather not build this from scratch, the Numbersmith Annual Budget and Net Worth Tracker fills a monthly budget from a transaction log, calculates the savings rate each month and year to date, and charts it against a target you set.