Templates

Building an Amortisation Schedule With Extra Payments in Excel

PMT, IPMT, and PPMT all break the moment you pay extra, because they assume a fixed schedule. Here is the row-by-row build that handles any extra payment pattern, with the two guard formulas that stop the last row going negative.

Depreciation in Excel: SLN, DB, DDB, SYD, and the One That Actually Reaches Salvage

Excel has five depreciation functions and one of them quietly leaves $510 of an $85,000 machine undepreciated. Here is the same asset through all of them, side by side, plus which one matches the tax method you probably need.

Weighted Averages and GPA in Excel: SUMPRODUCT, and Why AVERAGE Is Wrong

AVERAGE gives 86.0 where the real grade is 85.0, and 3.20 where the real GPA is 3.29. One SUMPRODUCT fixes both. Here is the build, the letter-grade lookup, and the formula that tells you what you need next semester.

Building a Debt Payoff Tracker in Excel That Handles the Rollover

The hard part is not the interest. It is routing a freed-up minimum payment to the next debt automatically. Here is the sheet that does it, run on five real debts, where switching methods saves $104 and the extra $400 a month saves $6,992.

EOQ and Reorder Points in Excel: One Row Per SKU, Four Formulas

A reorder sheet needs SQRT, NORM.S.INV, and a case-pack rounding step. Here is the four-column build across four SKUs, the conditional format that flags what to order today, and the input that everyone guesses wrong.

An Excel Invoice Template That Handles Mixed Taxable Lines

Most invoice templates apply one tax rate to the whole subtotal, which is wrong the moment one line is exempt. Here is the SUMIFS build that taxes only what should be taxed, plus the rounding decision that decides whether your ledger reconciles.

Budget vs Actual in Excel: The Sign Convention Everyone Gets Wrong

Actual minus budget is favourable on a cost line and unfavourable on a revenue line, so one subtraction cannot serve both. Here is the account-type formula that fixes it, plus the flexed budget that separates a real cost saving from selling less.

Comparing Loans in Excel With a Two-Variable Data Table

A data table fills 28 payment scenarios from one PMT formula, and it is the least-used feature in Excel for the amount of work it saves. Here is the exact setup, the corner cell rule that trips everyone, and the calculation setting that stops it freezing your workbook.

XLOOKUP for Rate Tables: Brackets, Tiers, and Effective Dates

Bracket lookups need match mode −1, and progressive tax needs more than a single lookup. Here are both, verified two ways on the same income, plus the SUMPRODUCT one-liner that replaces the whole bracket table.

PivotTables for Expense Categories: The Six Things That Go Wrong

Dates that will not group, a value field that counts instead of sums, and a calculated field that computes on the total rather than each row. Here is an expense pivot built properly, and the fix for every failure that stops people using them.

Gross to Net Pay in Excel: Four Deductions, Four Different Tax Bases

A retirement deferral escapes income tax and not payroll tax. A health premium escapes both. Getting the order of subtraction right is the whole exercise, and it is why one gross figure produces four different taxable bases.

Tiered Sales Commission in Excel: One SUMPRODUCT, and the Cliff to Avoid

A progressive tier structure pays $4,950 where a cliff structure pays $6,650 on the same $95,000. Here is the one-formula build for both, and the $2 sale that earns a rep $1,200 when the tiers are cliffs.

CAGR and Percentage Change in Excel: Counting Periods, Not Data Points

Seven years of revenue is six periods of growth, and using seven turns a 15.7% CAGR into 13.4%. Here is the formula, the three Excel functions that all compute it, and the percentage-point confusion that costs more than the arithmetic.

Descriptive Statistics in Excel: One Outlier Turns a 10 ms Spread Into 194

Fifteen response times, one bad measurement, and the standard deviation moves from 10.00 to 194.42. Here is the full descriptive block, the IQR rule that finds the culprit automatically, and the QUARTILE variant that gives a different answer.

Date Arithmetic in Excel: NETWORKDAYS, EDATE, EOMONTH, and the DATEDIF Trap

Adding 30 to a date lands on a Sunday. Adding one month to 31 January has no obvious answer. Here are the functions that handle both properly, plus the undocumented one that returns wrong answers and the two you should never leave in a saved file.

Savings Goals in Excel: PMT, NPER, RATE, and the Sign Convention

Four functions solve the same equation for four different unknowns, and all four fail the same way if the signs are inconsistent. Here is the full set worked on one $60,000 goal, plus the inflation adjustment that raises the required contribution by 21%.

Refinance Break-Even in Excel: The 17-Month Answer That Is Not the Answer

Closing costs divided by monthly saving gives 17 months and hides that the new loan restarts a 30-year clock. Here is the model that compares like with like, where keeping the old payment saves $159,809 instead of $18,907.

A Rental Property Cash Flow Model in Excel That Does Not Flatter the Deal

Most rental spreadsheets omit vacancy, management, and capital reserves, which is how a property that loses $296 a month shows a profit. Here is the full model on one deal, with the four return figures it should produce.

Sales Tax and VAT in Excel: Adding It, Extracting It, and the $8.50 Error

Removing 8.25% from a tax-inclusive price is not multiplying by 8.25%. It is dividing by 1.0825, and getting it wrong overstates the tax by $8.50 on a $1,353 invoice. Here are both directions, multiple rates, and a VAT return in ten cells.

Data Validation and Conditional Formatting: Making a Model Others Cannot Break

Validation does not survive a paste, which is the single most important thing to know about it. Here are the rules that actually hold, formula-based formatting that highlights whole rows, and the check cell every model should have.

Monte Carlo in Excel: Why Your Most Likely Estimate Is Wrong 83% of the Time

Adding up five most-likely task costs gives $121,500 and an 83% chance of overrun. Here is the simulation that produces the real distribution, built with NORM.INV, RAND, and a data table used as a trial engine.

Solver for Product Mix: Why the Highest-Margin Product Should Not Be Made

Two sensible heuristics give $59,160 and $63,000 of contribution. Solver gives $68,190 and makes none of the highest-margin product. Here is the setup, the constraint everyone forgets, and how to read the sensitivity report.

A Freelance Rate Model in Excel: Why $95,000 Take-Home Needs $151 an Hour

Dividing a target salary by 2,080 hours gives $46. The real answer is three times that, because of utilisation, self-employment tax, and the benefits an employer used to pay. Here is the model built backwards from take-home pay.

Scaling Recipes in Excel: The Four Ingredients That Do Not Multiply

Multiplying every line by 2.5 works for flour and ruins the leavening, the salt, the pan, and the bake time. Here is the spreadsheet with CONVERT, fraction formatting, and the baker’s percentage that makes scaling exact.

Break-Even and Operating Leverage in Excel: A 10% Sales Drop, a 31% Profit Drop

Break-even units is one division. The useful part is what sits around it: margin of safety, operating leverage, and the target-profit formula. Here is all four on one business, where sales are 33% above break-even and profit is three times as sensitive.

A 13-Week Cash Flow Forecast in Excel: Profitable and Out of Money

A business can be profitable every month and hit zero cash in week eleven, because profit is recognised when earned and cash arrives when collected. Here is the rolling forecast that shows it, and the two formulas that turn payment terms into dates.

Mortgage Affordability in Excel: What a $620 Car Payment Costs in House

Debt-to-income limits work backwards from income to a maximum payment, then from that payment to a price. On one household the car loan costs $80,000 of buying power and a 1% rate rise costs about $29,000. Here is the model.

An Energy Cost Tracker in Excel: Value Savings at the Marginal Rate, Not the Average

On a tiered tariff the last kilowatt-hour costs 24 cents while the average is 18. Every efficiency saving comes off the top tier, so valuing it at the average makes a 2.7-year payback look like 3.5. Here is the sheet that gets it right.

A Project Schedule in Excel: Dependencies, Working Days, and a Gantt Without an Add-In

Two formulas turn a task list into a schedule that skips weekends and holidays and moves when a predecessor slips. The Gantt bars are conditional formatting, not a chart, and the off-by-one in the finish date catches everyone.

Scenario Analysis in Excel: Skip Scenario Manager, Use a Selector Cell

Scenario Manager hides your assumptions inside a dialog nobody can audit. A scenario table with an INDEX selector does the same job in the open, updates live, and feeds the tornado chart that shows which input actually matters.

Build a Mortgage Calculator in Excel: PMT, IPMT, PPMT and a Full Schedule

A working mortgage calculator in a spreadsheet takes four formulas and about ten minutes. Here is the build, the sign convention that trips everyone up, and how to add extra payments.

Compound Interest in Excel: FV, Regular Contributions, and a Year-by-Year Table

The FV function handles compound growth with contributions in one line. Here is the build, the timing argument almost everyone leaves on the wrong setting, and how to separate real growth from inflation.

Break-Even Analysis in Excel: Formulas, Goal Seek, and a Data Table

Build a break-even model that answers four questions at once: how many units, at what revenue, what happens if costs rise, and what price you would need. Uses only built-in Excel features.

NPV and IRR in Excel: The Off-By-One Error Almost Everyone Makes

Excel NPV does not do what its name suggests. It discounts the first value by one period, which quietly misprices every project where the outlay happens today. Here is the correct build, plus XNPV, IRR, and when IRR lies.

Build a Timesheet in Excel: Overnight Shifts, Breaks, and Overtime That Actually Works

Time arithmetic in Excel breaks in three predictable places: shifts crossing midnight, decimal hours, and weekly overtime thresholds. Here is a timesheet that handles all three.

How to Build a Discount Calculator in Excel and Google Sheets (Formulas Explained)

Every discount calculation (percentage off, stacked discounts, reverse original-price lookups, and tiered bulk pricing) comes down to a handful of one-line formulas. Here is exactly how to set them up.

Templates16 min read

Business Budget Workbook Guide: Turn Department Spending Into a Repeatable Monthly Process

Run a business budget as a department-by-department workbook: plan targets, log actuals, read variance, and turn a spreadsheet into a repeatable monthly process.

How to Reconcile a Bank Statement in Excel, Step by Step

Your register says one number, your bank statement says another. Here's the exact formula that explains the gap and gets both balances to agree.

Templates9 min read

How to Build a Checkbook Register in Excel (Running Balance Formula Explained)

A checkbook register is just five columns and one formula. Here's exactly how to structure it so the running balance never breaks.

How to Calculate Weighted GPA by Hand (With a Full Worked Example)

Confused why your grade portal shows a different number than you expected? Here's exactly how weighted GPA is calculated, step by step, with a full worked example.

How to Build a Weekly Class Schedule That Does Not Fall Apart by Week 3

Most class schedules look great on day one and collapse by week three. Here's how to build a weekly schedule that actually holds up, with the study-hour math to back it.

Why Your Assignment Tracker Keeps Failing (And the Fix)

Started a to-do list in week one, abandoned it by week four? Here's why most assignment trackers fail, and the system that actually holds.

Templates17 min read

Financial Planning Workbook Guide: Use a Budget Spreadsheet as Your Control Center

Learn how to use a financial planning workbook to run a monthly budgeting process, track expenses, support savings decisions, and pair an offline spreadsheet with DTC calculators.