Financial Planning Excel Sheet: Free Build With Formulas

Quick Answer: You can build a free financial planning sheet in Excel with four tabs: net worth, goals, debt and emergency fund. Excel's built-in PMT, FV and NPER functions do the planning math, such as the monthly saving needed for a goal or the months left on a debt. Microsoft also offers free budget templates if you only need a starting layout.

A personal financial plan fits in one Excel workbook with four tabs: a net worth snapshot, a list of goals with target dates, a debt payoff schedule and an emergency fund line. Three built-in functions do the maths: PMT for the monthly saving a goal needs, FV for where your current saving leads, and NPER for months until a debt is paid. Refresh it every quarter from your statements.

You will not find a single "download this and you are done" file here, and that is deliberate. A plan built around someone else's categories never quite fits. Below are the exact layouts and formulas, with a worked example you can copy into a blank workbook in about half an hour. If you would rather start from a ready-made layout, Microsoft publishes free budget templates for Excel; you can bolt the planning tabs below onto any of them.

This is a tool for organizing your own numbers, not financial advice. The interest rates in the examples are made-up round figures chosen to show how the formulas work, not rates you can get today.

What goes in a personal financial plan spreadsheet?

Each tab feeds one number into a short summary at the top of the first sheet: net worth, total monthly saving committed, months to debt-free, and emergency months covered. Four numbers you can compare quarter to quarter.

Tab 1: the net worth snapshot

List every account and its balance on one date, usually the last day of the quarter. Take balances from statements, not from memory.

With Type in column B and Balance in column C, net worth is:

=SUMIF(B2:B7,"Asset",C2:C7)-SUMIF(B2:B7,"Liability",C2:C7)

Here that is 48,700 minus 36,200, a net worth of 12,500. Whether to include a car or a home at an estimated value is your call; what matters is doing it the same way every quarter so the trend means something. The net worth tracking guide goes deeper on valuing assets and building a history.

Tab 2: goals with dates and monthly amounts

A goal without a date is a wish. Give each goal four columns: target amount, target date, amount already saved toward it, and an assumed annual return on the money while it waits.

Excel's PMT function takes PMT(rate, nper, pv, [fv], [type]). Microsoft's remarks matter here: keep units consistent (a monthly payment needs a monthly rate, so divide the annual rate by 12 and multiply years by 12), and treat money you pay in as negative.

Worked example. You want 30,000 for a home deposit in 4 years. You have 6,000 saved toward it, held in savings that we will assume earns 4% a year.

=PMT(4%/12, 4*12, -6000, 30000)

Excel returns about -441.90: you need to put in roughly 441.90 a month. The negative sign is Excel telling you the money flows out of your pocket into the goal.

Now check what your current habit produces. If you are actually saving 400 a month, FV shows where you end up:

=FV(4%/12, 48, -400, -6000)

That gives about 27,823, roughly 2,177 short of the goal. Your choices are visible in one glance: save about 42 more a month, push the date back, or lower the target. Put a "gap" column next to each goal that subtracts the FV result from the target, and use conditional formatting to turn it red when it is positive.

A few rules that keep the goals tab honest:

• Each pound, dollar or rupee has one job. The 6,000 counted toward the deposit cannot also be your emergency fund.
• Short-term goals (under about three years) are usually held in cash, so use a low assumed rate or zero. With a zero rate, PMT simply divides the gap by the months.
• Write the assumed rate in its own cell and reference it. When rates move, you change one number and every goal updates.

Tab 3: debt payoff with NPER

For each debt, record the balance, the annual interest rate from your latest statement, and the monthly payment you actually make. NPER returns the number of periods needed: NPER(rate, pmt, pv, [fv], [type]).

Worked example. A credit card balance of 5,400 at an assumed 22% a year, paid at 250 a month:

=NPER(22%/12, -250, 5400)

The answer is about 27.8 months. Total payments come to roughly 6,940, so about 1,540 goes to interest.

Raise the payment to 350 and NPER drops to about 18.3 months, with total interest of roughly 1,005. That extra 100 a month saves about 530 in interest and roughly nine and a half months. Seeing that comparison on screen tends to change behaviour more than any rule of thumb.

Going the other way, if you want the card cleared in 24 months:

=PMT(22%/12, 24, 5400)

returns about -280.14 a month.

Add a column for each extra-payment scenario you want to compare. These figures assume you add no new charges and the rate stays fixed. Variable rates and new purchases change the answer, which is one reason to refresh the tab every quarter with the real statement balance.

Tab 4: the emergency fund line

This tab is arithmetic, but the input is where most plans go wrong. "Monthly spending" should be essential spending: rent or mortgage, utilities, food, insurance, minimum debt payments, transport. Pull it from three months of statements and average it rather than estimating.

Suppose your statements show essentials averaging 2,850 a month. A target of three to six months, a range many personal finance guides use, means 8,550 to 17,100. If the account set aside for emergencies holds 5,000, the formula =5000/2850 shows about 1.8 months covered.

Our emergency fund tracker guide shows how to tag essential versus optional spending from statement data so the average is real.

Where do the numbers come from each quarter?

The plan is only as current as its inputs. A quarterly routine:

1. Download the latest statement for every account and card.
2. Update balances on the net worth tab with the closing balance from each statement.
3. Update the debt tab with each statement's balance and interest rate.
4. Recalculate essential spending from the last three months of transactions.
5. Compare the four summary numbers with last quarter's and note why each moved.

Step 4 is the slow one if your bank gives you PDFs. Some banks offer a CSV download; use it when they do. When they do not, QuickBankConvert can turn uploaded statement PDFs into Excel rows with date, description, category and amount, which you can then filter to essentials. Either way, check that the total of the rows matches the statement's totals before trusting the average.

Using Google Sheets instead

The same plan works in Google Sheets. Google's help documents PMT with the arguments rate, number_of_periods, present_value and optional future_value and end_or_beginning, in the same order as Excel's, and FV has its own page in the same help center. The worked examples above carry over with the same arguments.

Keeping it useful

• Keep assumptions (rates, inflation if you use it, target dates) in one clearly labelled block.
• Date-stamp every quarterly snapshot on its own row so the history builds itself.
• Resist adding tabs. Retirement projections deserve their own model, and our retirement planning guide covers that separately.

Related guides

• Net worth tracking from bank statements
• Emergency fund tracker
• Retirement planning with bank statement analysis