Chapter 7 · 4 levels
Loans, savings and what-if
The money-over-time maths analysts get asked for: loan payments, savings growth, how long a repayment takes, and working backwards from a target, as Excel's Goal Seek does.
StartNot every question an analyst gets is about the past. “Can we afford the new racking?”, “How much will we have if we put 5% of revenue aside?”, “What is the biggest loan we can take on if the payment must stay under $2,000?” are questions about money over time, and the finance team expects the analyst to answer them as reliably as a revenue report.
All of them rest on one idea: money now and money later are not the same, because of interest.
Excel wraps the arithmetic in PMT, FV, NPER and Goal Seek. In this chapter you write the same
formulas in Python, so you know what those functions do and can check them, and you use Harbor &
Pine’s real 2025 revenue to size the numbers.
You’ll compute a loan payment and the interest it costs, grow a savings plan and count the months to repay a loan, spot the rate and period traps that make spreadsheet answers wrong by a factor of twelve, and finish with a what-if boss: the largest loan for a payment cap, the highest rate a budget can bear, and a scenario table for the finance meeting.
Levels
- What will the racking loan cost each month?
from Daniel Brooks, Head of Operations
loan payment, pmt, annuity, interest
- Save for it, or borrow and repay?
from Laura Bennett, Head of Finance
future value, fv, nper, compound interest
- The payment that came out twelve times too big
from Brian Mitchell, Finance Analyst
pmt, interest rate, periods, sign convention
- Boss: what can we afford?
from Laura Bennett, Head of Finance
goal seek, bisection, present value, scenario analysis, pmt