Lesson 7 · Excel: Zero to Expert
Excel Financial Functions Full Course: PMT, NPV, IRR & Loan Schedules | Excel 107
Sheet: TimeValue Workbook: excel107finance.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by…
- CourseExcel: Zero to Expert
- Lesson7 of 7
- Video1 h 8 min
- FormatExcel workbook · step-by-step guide
What you'll learn
Files for this lesson
Open the workbook in Excel (Microsoft 365 or Excel 2021) and follow along with the video.
- excel107_finance.xlsx48.4 KB
📝 Step-by-step guide
Excel 107: Financial Functions - Loans, Savings and Investments#
Sheet: TimeValue
Workbook: excel107_finance.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step with the video. Each section below lists exactly what to click and type.
The time value of money#
In A1 type
The time value of moneyFormat A1: bold, font size 14
Starting in F3, type these rows (one value per cell, moving right):
|
Interest rate|5%| |---|---|Starting in A3, type these rows (one value per cell, moving right):
|
Year|Simple|Compound| |---|---|---| |0|1000|1000|
Starting in A5, type these rows (one value per cell, moving right):
|
=A4+1|=B4+$B$4*$G$3|=C4*(1+$G$3)| |---|---|---|Fill A5:C14 down (drag the fill handle, or select it and press Ctrl+D)
Format B4:C14: number format currency
Format A3:C3: bold, font colour White, Background 1, fill Blue, Accent 1
Format F3: bold
AutoFit the width of column B:C (double-click its right edge)
After ten years, simple interest has turned our thousand dollars into fifteen hundred: fifty dollars a year, every year, always on the original thousand. Compound interest gives one thousand six hundred and twenty eight dollars eighty nine. In year one both columns earn fifty dollars, but by year ten the compound column is earning over seventy seven dollars a year.
Starting in F5, type these rows (one value per cell, moving right):
|
Rule of 72|=72/(G3*100)| |---|---|Starting in F6, type these rows (one value per cell, moving right):
|
Exact years|=NPER(G3,0,-1,2)| |---|---|Format G5:G6: number format 0.0
Format F5:F6: bold
AutoFit the width of column F:G (double-click its right edge)
The rule of seventy two says fourteen point four years, and the exact answer is fourteen point two.
- In G3 type
8%
Now the rule of seventy two says nine years, and the exact answer is nine point zero. Very close again. And in the table, our thousand dollars grows to two thousand one hundred and fifty eight dollars ninety two by year ten with compounding, while simple interest only reaches eighteen hundred.
Money in, money out: the sign convention#
In G3 type
5%Starting in F8, type these rows (one value per cell, moving right):
|
FV of 1000|=FV(G3,10,0,-1000)| |---|---|Starting in F9, type these rows (one value per cell, moving right):
|
Without the minus|=FV(G3,10,0,1000)| |---|---|Format G8:G9: number format currency
Format F8:F9: bold
AutoFit the width of column F:G (double-click its right edge)
The first gives one thousand six hundred and twenty eight dollars eighty nine, positive, exactly matching year ten of the compound column. Money out today, money back in ten years. The second gives the same amount, but negative. Excel read it as: the bank gives you a thousand dollars today, so you must pay back that much plus interest later. The maths is identical. Only the direction changed.
FV: growing your savings#
Sheet: Savings
In A1 type
Savings planFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Starting balance|2000| |---|---| |Monthly deposit|300| |Annual rate|4.5%| |Years|5|
Starting in A7, type these rows (one value per cell, moving right):
|
Future value|=FV(B5/12,B6*12,-B4,-B3)| |---|---| |Total deposited|=B3+B4*B6*12| |Interest earned|=B7-B8|
- Format B3:B4,B7:B9: number format currency
- Format A3:A9: bold
- Format B3:B6: fill Yellow
- AutoFit the width of column A:B (double-click its right edge)
After five years we'd have twenty two thousand six hundred and forty seven dollars twenty six. We put in twenty thousand dollars of our own money, so two thousand six hundred and forty seven dollars twenty six is interest.
Starting in D3, type these rows (one value per cell, moving right):
|
Year|Balance| |---|---|
- In E4 type the formula
=FV($B$5/12,D4*12,-$B$4,-$B$3) - Fill E4:E8 down (drag the fill handle, or select it and press Ctrl+D)
- Format E4:E8: number format currency
- Format D3:E3: bold, font colour White, Background 1, fill Blue, Accent 1
- AutoFit the width of column D:E (double-click its right edge)
Five thousand seven hundred and sixty seven dollars after one year, then nine thousand seven hundred and seven, thirteen thousand eight hundred and twenty eight, eighteen thousand one hundred and thirty nine, and twenty two thousand six hundred and forty seven. Each year's jump is a little bigger, because the growing balance earns more interest.
Starting in A11, type these rows (one value per cell, moving right):
|
Deposit at start|=FV(B5/12,B6*12,-B4,-B3,1)| |---|---|Starting in A12, type these rows (one value per cell, moving right):
|
Forgot to divide|=FV(B5,B6*12,-B4,-B3)| |---|---|Format B11:B12: number format currency
Format A11:A12: bold
Format B12: font colour Red
AutoFit the width of column A:B (double-click its right edge)
Depositing at the start of each month gives twenty two thousand seven hundred and twenty two dollars eighty. That's seventy five dollars fifty four more, because every deposit earns one extra month of interest. And the mistake claims one hundred and fourteen thousand nine hundred and four dollars, more than five times what we deposited. If a savings result looks too good to be true, check that the rate and the periods match.
- In B6 type
10
Forty eight thousand four hundred and ninety three dollars forty one. We deposited thirty eight thousand, so the interest is ten thousand four hundred and ninety three dollars forty one. We doubled the time, but the interest almost quadrupled. That's compounding again.
- In B6 type
5
PV: what a future sum is worth today#
Sheet: PresentValue
In A1 type
Present valueFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Future amount|20000| |---|---| |Years|5| |Annual rate|5%| |Worth today|=PV(B5,B4,0,-B3)|
- Format B3,B6: number format currency
- Format A3:A6: bold
- AutoFit the width of column A:B (double-click its right edge)
Fifteen thousand six hundred and seventy dollars fifty two. So twenty thousand dollars in five years is worth the same as about fifteen thousand six hundred and seventy today, because that's what would grow to twenty thousand at five percent. So sixteen thousand today would beat twenty thousand in five years.
Lump sum or monthly payments?#
Starting in A8, type these rows (one value per cell, moving right):
|
Lump sum now|150000| |---|---| |Monthly payment|1000| |Years|20| |Annual rate|5%| |Value today|=PV(B11/12,B10*12,-B9)| |Better choice|=IF(B12>B8,"Monthly payments","Lump sum")|
- Format B8:B9,B12: number format currency
- Format A8:A13: bold
- AutoFit the width of column A:B (double-click its right edge)
All two hundred and forty thousand dollars of payments are worth one hundred and fifty one thousand five hundred and twenty five dollars thirty one in today's money. Just a little more than the lump sum, so the verdict is Monthly payments. But it's close.
- In B11 type
6%
Now the payments are worth only one hundred and thirty nine thousand five hundred and eighty dollars seventy seven today, and the verdict flips to Lump sum.
RATE and NPER: credit cards and car deals#
Sheet: CreditCard
In A1 type
Credit card payoffFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Balance owing|5000| |---|---| |Annual rate|19.99%| |Monthly payment|150|
Starting in A6, type these rows (one value per cell, moving right):
|
Months to pay off|=NPER(B4/12,-B5,B3)| |---|---| |Years|=B6/12| |Total paid|=B5*B6| |Interest paid|=B8-B3|
- Format B3,B5,B8:B9: number format currency
- Format B6:B7: number format 0.0
- Format A3:A9: bold
- Format B3:B5: fill Yellow
- AutoFit the width of column A:B (double-click its right edge)
Forty nine point zero months. That's over four years to clear a five thousand dollar card. We'd pay seven thousand three hundred and fifty seven dollars in total, so two thousand three hundred and fifty seven dollars is interest. Almost half the original balance again.
Starting in D3, type these rows (one value per cell, moving right):
|
Payment|Months|Interest| |---|---|---|
Starting in E4, type these rows (one value per cell, moving right):
|
=NPER($B$4/12,-D4,$B$3)|=D4*E4-$B$3| |---|---|Fill E4:F6 down (drag the fill handle, or select it and press Ctrl+D)
Format D4:D6,F4:F6: number format currency
Format E4:E6: number format 0.0
Format D3:F3: bold, font colour White, Background 1, fill Blue, Accent 1
AutoFit the width of column D:F (double-click its right edge)
At one fifty a month: forty nine months and two thousand three hundred and fifty seven dollars of interest. At two fifty: twenty four and a half months and one thousand one hundred and thirty one dollars seventy eight. At four hundred: just over fourteen months, and six hundred and fifty two dollars ninety seven. Paying two hundred and fifty dollars more each month saves over seventeen hundred dollars of interest. The faster you pay, the less you pay.
In D7 type
80Fill E6:F7 down (drag the fill handle, or select it and press Ctrl+D)
Starting in A11, type these rows (one value per cell, moving right):
|
Interest each month|=B3*B4/12| |---|---|Format B11,D7: number format currency
Format A11: bold
AutoFit the width of column A:B (double-click its right edge)
NPER returns hash NUM. That's not Excel failing. The first month's interest is eighty three dollars twenty nine, more than our eighty dollar payment, so the balance never goes down. This debt would never be paid off. Hash NUM from NPER usually means the payment doesn't cover the interest, or the signs aren't opposite.
RATE: the hidden interest rate#
Starting in A13, type these rows (one value per cell, moving right):
|
Car price|28000| |---|---| |Monthly payment|595| |Months|60| |Monthly rate|=RATE(B15,-B14,B13)| |Annual rate|=B16*12| |Total interest|=B14*B15-B13|
- Format B16:B17: number format 0.00%
- Format B13:B14,B18: number format currency
- Format A13:A18: bold
- AutoFit the width of column A:B (double-click its right edge)
The monthly rate is zero point eight three percent, which makes an annual rate of ten point zero one percent. Over five years that's seven thousand seven hundred dollars of interest. That's expensive for a car loan, and now you can check any offer in seconds.
- In B14 type
550
The rate drops to six point six six percent a year, and the interest falls to five thousand dollars.
IPMT and PPMT: where each payment goes#
Sheet: Split
In A1 type
Payment splitFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Loan|28000| |---|---| |Annual rate|7.5%| |Years|5| |Monthly payment|=PMT(B4/12,B5*12,-B3)|
- Format B3,B6: number format currency
- Format A3:A6: bold
- AutoFit the width of column A:B (double-click its right edge)
Five hundred and sixty one dollars six a month.
Starting in D3, type these rows (one value per cell, moving right):
|
Month|Interest|Principal|Total| |---|---|---|---|
Starting in E4, type these rows (one value per cell, moving right):
|
=IPMT($B$4/12,D4,$B$5*12,-$B$3)|=PPMT($B$4/12,D4,$B$5*12,-$B$3)|=E4+F4| |---|---|---|Fill E4:G7 down (drag the fill handle, or select it and press Ctrl+D)
Format E4:G7: number format currency
Format D3:G3: bold, font colour White, Background 1, fill Blue, Accent 1
AutoFit the width of column D:G (double-click its right edge)
In month one, one hundred and seventy five dollars is interest, and only three hundred and eighty six dollars six goes off the loan. By month thirty it's ninety eight dollars fifty four of interest, and four hundred and sixty two fifty two of principal. And in the last month, just three dollars forty eight of interest, with five hundred and fifty seven fifty eight paying off the final piece. The total column is five hundred and sixty one dollars six every time. The payment never changes, only the mix inside it.
A full amortisation schedule#
Sheet: Schedule
In A1 type
Loan scheduleFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Loan|12000| |---|---| |Annual rate|9.5%| |Months|18| |Start date|1-Dec-2026| |Payment|=PMT(B4/12,B5,-B3)|
- Format B3,B7: number format currency
- Format B6: number format d mmm yyyy
- Format A3:A7: bold
- Format B3:B6: fill Yellow
- AutoFit the width of column A:B (double-click its right edge)
The payment is seven hundred and seventeen dollars ninety three a month.
Starting in D3, type these rows (one value per cell, moving right):
|
Month|Date|Payment|Interest|Principal|Balance| |---|---|---|---|---|---|Starting in D4, type these rows (one value per cell, moving right):
|
0|=B6|||| =B3| |---|---|---|---|Starting in D5, type these rows (one value per cell, moving right):
|
=D4+1|=EDATE($B$6,D5)|=$B$7|=I4*$B$4/12|=F5-G5|=I4-H5| |---|---|---|---|---|---|
Month one: a payment of seven hundred and seventeen dollars ninety three. Ninety five dollars is interest, one month at nine and a half percent on twelve thousand dollars, and six hundred and twenty two dollars ninety three reduces the balance to eleven thousand three hundred and seventy seven dollars seven.
Fill D5:I22 down (drag the fill handle, or select it and press Ctrl+D)
Starting in D23, type these rows (one value per cell, moving right):
|
Total|| =SUM(F5:F22)|=SUM(G5:G22)|=SUM(H5:H22)| |---|---|---|---|
- Format E4:E22: number format d mmm yyyy
- Format F4:I23: number format currency
- Format D3:I3: bold, font colour White, Background 1, fill Blue, Accent 1
- Format D23:I23: bold, double bottom border
- AutoFit the width of column D:I (double-click its right edge)
The dates run from the first of January twenty twenty seven to the first of June twenty twenty eight. Each month the interest falls a little, from ninety five dollars down to five dollars sixty four in the final month, while the principal rises. The balance after month eighteen is zero, which proves the schedule works. If you see a tiny minus sign in front of that zero, it's only a rounding leftover, far smaller than a cent. In total we pay twelve thousand nine hundred and twenty two dollars sixty six: twelve thousand of principal, and nine hundred and twenty two dollars sixty six of interest.
Starting in A9, type these rows (one value per cell, moving right):
|
Interest, all months|=-CUMIPMT(B4/12,B5,B3,1,B5,0)| |---|---| |First 12 months|=-CUMIPMT(B4/12,B5,B3,1,12,0)| |Schedule, months 1-12|=SUM(G5:G16)|
- Format B9:B11: number format currency
- Format A9:A11: bold
- AutoFit the width of column A:B (double-click its right edge)
CUMIPMT gives nine hundred and twenty two dollars sixty six for the whole loan, exactly matching our total row. For the first twelve months it's eight hundred and five dollars seventy eight, and the sum from our schedule agrees to the cent. Handy for a financial year's interest.
- In B4 type
12% - AutoFit the width of column F:I (double-click its right edge)
The payment rises to seven hundred and thirty one dollars seventy eight, the first month's interest is now one hundred and twenty dollars, and the total interest grows to one thousand one hundred and seventy two dollars twelve. The balance still lands on zero after eighteen months.
- In B4 type
9.5%
Extra repayments and the interest they save#
Sheet: Extra
In A1 type
Extra repaymentsFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Loan|35000| |---|---| |Annual rate|8.2%| |Years|7|
- Format B3: number format currency
- Format A3:A5: bold
- Format B3:B5: fill Yellow
Starting in A7, type these rows (one value per cell, moving right):
|
Scenario|Normal|Plus 100|Plus 250| |---|---|---|---| |Extra each month|0|100|250| |Monthly payment|=PMT($B$4/12,$B$5*12,-$B$3)+B8| | | |Months|=NPER($B$4/12,-B9,$B$3)| | | |Years|=B10/12| | | |Total interest|=B9*B10-$B$3| | | |Interest saved|=$B$12-B12| | |
- Fill B9:D13 right (Ctrl+R)
- Format B8:D9,B12:D13: number format currency
- Format B10:D11: number format 0.0
- Format A7:D7: bold, font colour White, Background 1, fill Blue, Accent 1
- Format A8:A13: bold
- AutoFit the width of column A:D (double-click its right edge)
The normal payment is five hundred and forty nine dollars one. The loan takes eighty four months, seven years, and costs eleven thousand one hundred and sixteen dollars ninety seven in interest. Add just one hundred dollars a month and it's paid off in sixty seven and a half months, about five and a half years. The interest drops to eight thousand eight hundred and seven dollars, a saving of two thousand three hundred and nine dollars. Add two hundred and fifty, and it's gone in fifty two months, under four and a half years, with four thousand three hundred and eighty one dollars of interest saved.
NPV and IRR: comparing two projects#
Sheet: Projects
In A1 type
Two projectsFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Discount rate|8%| |---|---|Starting in A5, type these rows (one value per cell, moving right):
|
Year|Project A|Project B| |---|---|---| |Start|-60000|-60000| |Year 1|8000|35000| |Year 2|12000|25000|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Year 3|18000|15000| |---|---|---| |Year 4|26000|5000| |Year 5|36000|2000|Format B6:C11: number format currency
Format A5:C5: bold, font colour White, Background 1, fill Blue, Accent 1
Format A3: bold
Format B3: fill Yellow
Starting in A12, type these rows (one value per cell, moving right):
|
Total returned|=SUM(B7:B11)|=SUM(C7:C11)| |---|---|---| |NPV|=NPV($B$3,B7:B11)+B6|=NPV($B$3,C7:C11)+C6| |IRR|=IRR(B6:B11)|=IRR(C6:C11)|
- Format B12:C13: number format currency
- Format B14:C14: number format 0.0%
- Format A12:A14: bold
- AutoFit the width of column A:C (double-click its right edge)
On plain totals, A returns one hundred thousand dollars and B returns eighty two thousand. In today's dollars, A's NPV is fifteen thousand five hundred and ninety six dollars twenty two, and B's is ten thousand seven hundred and eighty four dollars sixty eight. Both are positive, so both beat eight percent, and A adds more value. But look at IRR. A earns fifteen point three percent, and B earns eighteen point two percent. B has the higher rate of return, because its money comes back sooner.
When NPV and IRR disagree#
- In B3 type
14%
Project A's NPV collapses to two thousand four hundred and ninety two dollars, while project B still has four thousand and sixty two. Now B wins on both measures. The IRRs didn't change, because IRR ignores our discount rate. They swap places at about twelve point three percent. So know your required return, and test a few rates before you decide.
In B3 type
8%Starting in A16, type these rows (one value per cell, moving right):
|
Wrong NPV|=NPV($B$3,B6:B11)| |---|---|Format B16: number format currency, font colour Red
Format A16: bold
Fourteen thousand four hundred and forty dollars ninety five, instead of fifteen thousand five hundred and ninety six. Excel's NPV assumes the first value arrives one period from now, so it wrongly discounted our day one cost by a whole year. Keep the start outside NPV, and add it on.
- Select A5:C11 and insert a column chart titled “Cash flows by year”
You can see it at a glance. B's tall bars come early, and A's come late. That timing is exactly what NPV and IRR are weighing up.
XNPV and XIRR with real dates#
Sheet: Dates
In A1 type
Dated cash flowsFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Discount rate|8%| |---|---|Starting in A5, type these rows (one value per cell, moving right):
|
Date|Cash flow|Days| |---|---|---| |1-Jul-2026|-15000| | |20-Oct-2026|4000| | |15-Mar-2027|3500| |
Starting in the next empty row, type these rows (one value per cell, moving right):
|
30-Sep-2027|5000| |---|---| |1-Jun-2028|6000|In C6 type the formula
=A6-$A$6Fill C6:C10 down (drag the fill handle, or select it and press Ctrl+D)
- Format A6:A10: number format d mmm yyyy
- Format C6:C10: number format general
- Format B6:B10: number format currency
- Format A5:C5: bold, font colour White, Background 1, fill Blue, Accent 1
The payments arrive after one hundred and eleven days, two hundred and fifty seven, four hundred and fifty six, and seven hundred and one days.
Starting in A12, type these rows (one value per cell, moving right):
|
XNPV|=XNPV(B3,B6:B10,A6:A10)| |---|---| |XIRR|=XIRR(B6:B10,A6:A10)| |IRR ignoring dates|=IRR(B6:B10)|Format B12: number format currency
Format B13:B14: number format 0.0%
Format A3,A12:A14: bold
AutoFit the width of column A:C (double-click its right edge)
XNPV is one thousand nine hundred and forty dollars eight. Positive, so at eight percent this investment is worth making. XIRR says it returns twenty point five percent a year. The ordinary IRR, which pretends each payment is a full year apart, says just eight point two percent. Our money actually came back in under two years, so the true return is far higher. Ignore the dates, and you'd badly undervalue this deal.
Depreciation: SLN and DB for a work vehicle#
Sheet: Depreciation
In A1 type
Ute depreciationFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Cost|52000| |---|---| |Salvage value|14000| |Life in years|5| |SLN per year|=SLN(B3,B4,B5)| |DB rate|=ROUND(1-(B4/B3)^(1/B5),3)|
- Format B3:B4,B6: number format currency
- Format B7: number format 0.0%
- Format A3:A7: bold
- AutoFit the width of column A:B (double-click its right edge)
Straight line depreciation is seven thousand six hundred dollars a year: the thirty eight thousand dollars of lost value, spread over five years. And the declining balance rate is twenty three point one percent of whatever value is left each year.
Starting in D3, type these rows (one value per cell, moving right):
|
Year|SLN|SLN value|DB|DB value| |---|---|---|---|---|Starting in D4, type these rows (one value per cell, moving right):
|
1|=$B$6|=$B$3-SUM($E$4:E4)|=DB($B$3,$B$4,$B$5,D4)|=$B$3-SUM($G$4:G4)| |---|---|---|---|---|In D5 type the formula
=D4+1Fill D5:D8 down (drag the fill handle, or select it and press Ctrl+D)
Fill E4:H8 down (drag the fill handle, or select it and press Ctrl+D)
- Format E4:H8: number format currency
- Format D3:H3: bold, font colour White, Background 1, fill Blue, Accent 1
- AutoFit the width of column D:H (double-click its right edge)
Straight line takes seven thousand six hundred every year, so the value falls evenly: forty four thousand four hundred, thirty six thousand eight hundred, and so on, to exactly fourteen thousand after year five. Declining balance takes twelve thousand and twelve dollars in year one, nine thousand two hundred and thirty seven dollars twenty three in year two, and just four thousand two hundred dollars sixty nine in year five. The value left after five years is thirteen thousand nine hundred and eighty four dollars twelve, a whisker under our salvage value, because the rate was rounded.
Break-even point for a small business#
Sheet: BreakEven
In A1 type
Coffee cartFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Fixed costs per month|6800| |---|---| |Price per coffee|5.2| |Variable cost per coffee|1.6| |Contribution per coffee|=B4-B5|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Break-even coffees|=B3/B6| |---|---| |Rounded up|=ROUNDUP(B7,0)| |Coffees per day|=B8/30| |Break-even sales|=B8*B4|
- Format B3:B6,B10: number format currency
- Format B7: number format 0.00
- Format B9: number format 0.0
- Format A3:A10: bold
- Format B3:B5: fill Yellow
- AutoFit the width of column A:B (double-click its right edge)
Each coffee contributes three dollars sixty. Six thousand eight hundred divided by three sixty is one thousand eight hundred and eighty eight point eight nine, so the cart needs to sell one thousand eight hundred and eighty nine coffees a month. That's about sixty three coffees a day, or nine thousand eight hundred and twenty two dollars eighty of sales.
Starting in D3, type these rows (one value per cell, moving right):
|
Coffees|Revenue|Total cost|Profit| |---|---|---|---|In D4 type the formula
=SEQUENCE(7,1,0,500)In E4 type the formula
=D4#*$B$4In F4 type the formula
=$B$3+D4#*$B$5In G4 type the formula
=E4#-F4#
- Format E4:G10: number format currency
- Format D4:D10: number format #,##0
- Format D3:G3: bold, font colour White, Background 1, fill Blue, Accent 1
- AutoFit the width of column D:G (double-click its right edge)
At fifteen hundred coffees we're still one thousand four hundred dollars behind. At two thousand coffees we've just crossed the line, with four hundred dollars profit, and at three thousand coffees the profit is four thousand dollars.
- Select D3:F10 and insert a scatter_lines chart titled “Coffee cart break-even”
Revenue starts at zero and climbs steeply. Total cost starts at six thousand eight hundred and climbs more gently. Where they cross, just below two thousand coffees, is the break-even point. To the left is loss, and to the right is profit.
- In B4 type
5.8
The contribution rises to four dollars twenty, and break-even falls to one thousand six hundred and twenty coffees a month, about fifty four a day. The table and the chart moved too: the revenue line is steeper, so it crosses the cost line sooner.
Project: Home loan and savings planner#
Sheet: Deposit
Saving the deposit#
In A1 type
Deposit plannerFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Property price|625000| |---|---| |Deposit percent|20%| |Deposit goal|=B3*B4| |Saved so far|40000|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Monthly saving|2000| |---|---| |Savings rate|4.75%| |Start date|1-Dec-2026| |Months to goal|=NPER(B8/12,-B7,-B6,B5)| |Goal date|=EDATE(B9,ROUNDUP(B10,0))|
- Format B3,B5:B7: number format currency
- Format B10: number format 0.0
- Format B9,B11: number format d mmm yyyy
- Format A3:A11: bold
- Format B3:B4,B6:B9: fill Yellow
- AutoFit the width of column A:B (double-click its right edge)
Her goal is a deposit of one hundred and twenty five thousand dollars. NPER says thirty six point seven months, so she'll get there on the first of January twenty thirty, just over three years from now.
Starting in A12, type these rows (one value per cell, moving right):
|
Balance after 3 years|=FV(B8/12,36,-B7,-B6)| |---|---| |Saving for 30 months|=PMT(B8/12,30,B6,-B5)| |Loan needed|=B3-B5|Format B12:B14: number format currency
Format A12:A14: bold
AutoFit the width of column A:B (double-click its right edge)
After exactly three years she'd have one hundred and twenty three thousand three hundred and thirty one dollars eighty nine, just short of the goal, which agrees with NPER's thirty six point seven months. To get there in thirty months, she'd need to save two thousand five hundred and fifteen dollars seventy a month. And the loan she'll need is five hundred thousand dollars.
- In B7 type
2500
Now it takes thirty point two months, and the goal date moves to the first of July twenty twenty nine, six months sooner.
- In B7 type
2000
The home loan#
Sheet: HomeLoan
In A1 type
Home loan plannerFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Loan amount|=Deposit!B14| |---|---| |Annual rate|5.99%| |Years|30| |Extra per month|200|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Monthly repayment|=PMT(B4/12,B5*12,-B3)| |---|---| |Total repaid|=B7*B5*12| |Total interest|=B8-B3| |Interest in year one|=-CUMIPMT(B4/12,B5*12,B3,1,12,0)|
- Format B3,B6:B10: number format currency
- Format A3:A10: bold
- Format B4:B6: fill Yellow
- AutoFit the width of column A:B (double-click its right edge)
The monthly repayment is two thousand nine hundred and ninety four dollars fifty four. Over thirty years Priya would repay one million and seventy eight thousand dollars, so the total interest is five hundred and seventy eight thousand and thirty four dollars. More than the loan itself. In the first year alone, twenty nine thousand seven hundred and eighty two dollars ninety four is interest. Of almost thirty six thousand dollars paid that year, only about six thousand one hundred and fifty reduces the loan.
Starting in A11, type these rows (one value per cell, moving right):
|
Months with extra|=NPER(B4/12,-(B7+B6),B3)| |---|---| |Years with extra|=B11/12| |Interest with extra|=(B7+B6)*B11-B3| |Interest saved|=B9-B13| |Years saved|=B5-B12| |Rate plus 3%|=PMT((B4+3%)/12,B5*12,-B3)|
- Format B11:B12,B15: number format 0.0
- Format B13:B14,B16: number format currency
- Format A11:A16: bold
- AutoFit the width of column A:B (double-click its right edge)
With an extra two hundred dollars a month, the loan is paid off in three hundred and five point three months, about twenty five and a half years. That's four point six years sooner. The interest falls to about four hundred and seventy five thousand dollars, a saving of roughly one hundred and two thousand nine hundred dollars. Two hundred dollars a month saves over a hundred thousand dollars. That's the power of paying down principal early. And the stress test: at eight point nine nine percent, the repayment would be four thousand and nineteen dollars fifty two a month, over a thousand dollars more. Priya should be sure she could handle that.
Charting the balance over time#
Starting in D3, type these rows (one value per cell, moving right):
|
Year|Balance|With extra| |---|---|---|In D4 type the formula
=SEQUENCE(B5+1,1,0)In E4 type the formula
=FV($B$4/12,D4#*12,$B$7,-$B$3)In F4 type the formula
=LET(bal,FV($B$4/12,D4#*12,$B$7+$B$6,-$B$3),IF(bal<0,0,bal))
- Format E4:F34: number format $#,##0
- Format D3:F3: bold, font colour White, Background 1, fill Blue, Accent 1
- AutoFit the width of column D:F (double-click its right edge)
After ten years, Priya still owes four hundred and eighteen thousand three hundred and seventeen dollars on the normal plan. Only about eighty two thousand dollars of the loan is gone. With the extra two hundred a month, she owes three hundred and eighty five thousand five hundred and fifty nine, already almost thirty three thousand dollars less.
- Select D3:F34 and insert a scatter_lines chart titled “Loan balance over time”
The normal balance falls slowly at first, because early repayments are mostly interest, then faster and faster towards the end, reaching zero at year thirty. The extra repayments line pulls away more and more each year. By year twenty, the normal plan still owes two hundred and sixty nine thousand eight hundred and fifty dollars, but the extra plan owes one hundred and seventy seven thousand five hundred and fifty two. And it hits zero during year twenty six.
- In B6 type
500
The loan is gone in two hundred and fifty one point five months, about twenty one years, nine years early. The interest saved is roughly one hundred and ninety nine thousand dollars, and the chart line drops much faster too.
- In B6 type
200 - In H19 type
Plan summary - In H20 type
Deposit of $125,000 reached in January 2030 - In H21 type
Repayment about $2,995 a month over 30 years - In H22 type
An extra $200 a month saves over $100,000 of interest
- Format H19: bold, font size 12
- Format H20:H22: italic
- Click A1
Recap and what's next#
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



