Who asked: Dana and Lee are buying a $450,000 home with 20% down and want to understand their mortgage before they sign. (They're made up, and this is practice, not advice.)
The data: the inputs at the top of the Planner sheet: price, down payment, two rates and terms, an extra payment they could afford, and a refinance offer they expect in five years.
What done looks like: one sheet that answers their four questions from the inputs alone: what each loan costs a month and in total, how much an extra payment saves, and whether refinancing later would pay. Every answer is a formula, so changing an input updates the plan.
This project puts the time value of money functions to work on one decision. You'll build a planner one step at a time, in one workbook that carries over from step to step:
- the loan amount;
- the payment on a 30-year and a 15-year loan (PMT);
- the total interest on each;
- the months saved by an extra payment (NPER);
- the balance still owed after five years (FV);
- the new payment after refinancing, and the saving;
- how long the saving takes to cover the refinance costs;
- the decision.
Every answer points at the input cells, so the planner works for any home, rate or offer. That's also how it's checked: each step runs again on different inputs.