Skip to content
Tutorials Center
My learning

Topics

Lesson 86 of 93 ยท Real-World Projects

Project: Loan and Savings Planner

Build an Excel loan and savings planner: a PMT amortization schedule, extra payments, FV savings goals and a what-if table, with runnable stand-ins.

Two money questions come up in almost every household: "What will this loan really cost me?" and "How much must I save each month to reach a goal?" In this project you build one workbook that answers both. It has a full amortization schedule that shows where every payment goes, an extra payment input that shows how much interest you save, a savings goal calculator, and a what-if table that compares many rates and terms side by side.

The financial functions themselves are explained in Financial Functions: PMT, NPV, IRR and More; here you put them to work. Excel cannot run on this site. The formulas appear in plain blocks (type them in Excel), and the runnable TryIts are stand-ins: a small JavaScript spreadsheet engine that evaluates the same formulas, plus real Python with pandas for the comparisons.

One workbook, three tools

Loan schedule Payment, interest, principal, balance.
Extra payments See time and interest saved.
Savings goal Monthly deposit for a target.
What-if table Compare rates and terms.
1 / 2

Step 1: the input cells

Put every assumption in its own labeled cell, away from the formulas, and give the cells names if you like (named ranges):

Cell Label Example
B1 Loan amount 250,000
B2 Annual interest rate 6%
B3 Term in years 30
B4 Extra payment per month 0
B5 Monthly payment (formula) =ROUND(-PMT(B2/12,B3*12,B1),2)

Two things to notice. The rate is divided by 12 and the years are multiplied by 12 so both are monthly, as the financial functions lesson requires. And the minus sign in front of PMT turns the negative cash-flow result into a positive payment you can read. Rounding to cents matches how a lender bills.

Step 2: the schedule

Each row of the schedule is one month. Row 8 is the first month; the row above it holds the header. A month works like this:

  1. Interest is the previous balance times the monthly rate.
  2. Payment is the planned payment plus the extra amount, but never more than what is still owed.
  3. Principal is the part of the payment that is left after interest.
  4. Balance is the previous balance minus the principal.
TEXT
A8:  1                              (A9: =A8+1, and so on)
B8:  =ROUND($B$1*$B$2/12,2)         Interest      (row 9: =ROUND(E8*$B$2/12,2))
C8:  =IF(A8=$B$3*12,$B$1+B8,MIN($B$5+$B$4,$B$1+B8))      Payment
D8:  =C8-B8                         Principal
E8:  =ROUND($B$1-D8,2)              Balance       (row 9: =ROUND(E8-D9,2))

The first row refers to the loan amount $B$1 as the "previous balance"; every later row refers to the balance above it (E8, E9, ...). Fill the formulas down for 360 rows (30 years), or more if you allow longer terms. Because absolute references lock the input cells and the relative references walk down the column, one filled formula serves every row.

The MIN in the payment formula is what makes extra payments safe: in the final month the payment shrinks to exactly the remaining balance plus its interest, and afterwards every row shows zeros. The IF(A8=$B$3*12, ...) part pays off any cents of rounding drift in the last scheduled month.

Step 3: run the schedule in the engine

The engine below evaluates these exact formulas. It is our stand-in for Excel's calculation: cells that start with = are translated to JavaScript, with PMT, MIN, ROUND, SUM, COUNTIF and IF provided. The same function builds the schedule twice, once without and once with an extra payment, so you can compare.

JS The amortization schedule with formulas

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The numbers show the main lesson of amortization: in month 1 almost the whole payment is interest, and in month 360 almost none is. Even a modest extra payment cuts years from the loan because it reduces the balance that all future interest is charged on.

Step 4: a summary block

Next to the inputs, add a few outputs. With the schedule in rows 8 to 367:

TEXT
Total interest:     =SUM(B8:B367)
Total paid:         =SUM(C8:C367)
Months to payoff:   =COUNTIF(C8:C367,">0")
Payoff date:        =EDATE(StartDate, B? - 1)      (months to payoff in B?)

The date line uses EDATE from date calculations. To show "interest saved" you need the no-extra total to compare with. The simplest approach: keep a second, hidden column set with extra fixed at 0, or compute the baseline interest closed-form: =-CUMIPMT(B2/12,B3*12,B1,1,B3*12,0) (the CUMIPMT function adds the interest of a range of periods).

Step 5: the savings goal calculator

For saving, ask the question the other way round: "I want 20,000 in 5 years at 4% a year. What should I deposit every month?" The function for the missing payment is PMT again, with a future value instead of a present value:

TEXT
Monthly deposit:   =-PMT(B8/12,B9*12,0,B7)     (B7 goal, B8 rate, B9 years)
Final balance:     =FV(B8/12,B9*12,-B10)       (B10 = deposit per month)
Months needed:     =NPER(B8/12,-B10,0,B7)      (how long a given deposit takes)

The sign rules from the financial functions lesson apply: deposits you pay in are negative, the goal you receive is positive. FV(rate, nper, pmt, [pv]), with pmt negative, gives a positive balance.

Step 6: a what-if table

How does the payment change if the rate is 5%, 6% or 7%, and the term 15, 20 or 30 years? You could type a formula in each cell of a grid, using mixed references ($A12 for the term and B$11 for the rate), or let Excel do it with a two-variable Data Table (Data, What-If Analysis, Data Table) as described in What-If Analysis. Either way the grid answers many questions at once.

The following stand-in builds the same grid with plain JavaScript functions, and also computes the savings deposit needed to reach a goal at several rates.

JS What-if grid and savings goals

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Look at the 0% row: with no interest the deposit is just the goal divided by the number of months. Every extra percent of interest lets the money do some of the work, and the longer the period the more it helps.

Step 7: compare extra payment options with pandas

Analysts often prototype a model in Python before building it in Excel, or use pandas to check an Excel schedule. This real-Python TryIt computes payoff time and total interest for several extra amounts, which is the sort of table a data table would produce.

Python Extra payment comparison in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Checking and hardening the workbook

  • Check by round trip. The last balance must be 0.00, and the sum of the principal column must equal the loan amount. Put both checks in cells that show OK or CHECK.
  • Validate inputs. Use data validation: rate between 0 and 1, term a whole number from 1 to 40.
  • Handle a zero rate. PMT works with a rate of 0 in Excel, but a hand-built interest formula should still be tested with it.
  • Plan for longer terms. Schedule rows beyond the term should show blanks, for example by wrapping formulas in IF($A8>$B$3*12,"",...).
  • Add a chart. A line chart of balance and a stacked column of interest versus principal make the story clear (chart basics).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is the payment formula =ROUND(-PMT(B2/12,B312,B1),2) written with B2/12 and B312?

    Rate and number of periods must use the same unit. Monthly payments need the monthly rate and the number of months.
  2. 2. In the schedule, what is the interest part of a month calculated from?

    Interest is charged on what is still owed, so it shrinks as the balance falls.
  3. 3. What does MIN($B$5+$B$4, previousBalance+interest) protect against?

    With extra payments the last payment must be only the remaining balance plus interest, and later rows become zero.
  4. 4. Which formula gives the monthly deposit needed to reach a goal in B7 with rate B8 over B9 years?

    PMT with pv 0 and fv equal to the goal returns the (negative) deposit, so a minus sign shows it as a positive amount.
  5. 5. Why do extra payments save so much interest?

    Each extra payment goes straight to principal, so every following month's interest is calculated on a smaller balance.

Summary

  • Keep all assumptions in labeled input cells and refer to them with absolute references.
  • A schedule row is interest (previous balance times monthly rate), payment, principal and new balance; MIN caps the final payment.
  • PMT, FV and NPER answer loan and savings questions; mind the signs and keep rate and periods in the same unit.
  • A two-variable data table or a mixed-reference grid compares many rates and terms at once.
  • The JavaScript engine and the pandas code are stand-ins that run the same arithmetic; Excel itself runs on your computer.

Next: Project: Gradebook or Employee Attendance Sheet.