Level 7 · Boss: can we fund a second warehouse?

A funding plan in three moves

Robert’s question chains every finance function in the chapter. Money saved now grows into a future sum (FV). A payment the business can afford supports a loan of a certain size today (PV). And PMT checks the loan the other way round. The sheet holds 2025’s completed revenue by month in A1:B13 and the plan’s inputs in E1:E7; you fill E9:E14.

Step 1: savings with FV

=FV(rate, nper, pmt, [pv], [type])
E9   =AVERAGE(B2:B13)*E1
E10  =FV(E2/12,E3*12,-E9)

The average month of 2025 brought in $191,807.51, so the store sets aside $5,754.23 a month. FV answers “what will these deposits be worth at the end?”: 60 monthly deposits at 4% a year (0.333% a month) reach $381,499.26. The deposits alone add up to $345,253.52; the other $36,245.74 is the return they earn while they wait.

The sign rule from the last level holds: each deposit leaves the business’s account, so it is negative, and the pot you get back at the end is positive. The optional pv is a lump sum already saved (0 here) and type 1 would mean depositing at the start of each month.

Step 2: the largest loan, with PV

How you’d do it with Goal Seek

In Excel, the classic way to answer “how big a loan can a $4,500 payment carry?” is Goal Seek, which works a formula backwards. You’d set it up like this:

1. Type a guess for the loan in a cell, say E11, and in E12 write =PMT(E4/12,E5*12,-E11).
2. Go to Data → What-If Analysis → Goal Seek.
3. Set cell: E12 (the cell with the formula). To value: 4500. By changing cell: E11 (the input, which must hold a typed value, not a formula).
4. Click OK. Excel tries values for E11 until E12 shows 4,500, then offers to keep the result.

Goal Seek is useful on any model that has no reverse formula: a price that hits a target margin, a conversion rate that reaches a revenue goal. Its weaknesses: it changes one cell only, it writes a typed number into the input, and it does not rerun when the rate or the payment changes later. Google Sheets offers it through the free Goal Seek add-on. This page can’t open Excel’s dialog, so here you use the formula that gives the same answer.

The direct answer: PV

For a loan, Goal Seek is searching for something Excel can compute directly. The loan a payment can carry is the present value of that stream of payments:

=PV(rate, nper, pmt, [fv], [type])
E11  =PV(E4/12,E5*12,-E6)

120 payments of $4,500 at 6.5% a year are worth $396,308.25 today. That is the most a lender would lend against them. The payments total $540,000; the difference, $143,691.75, is the interest over ten years.

Step 3: check it, then answer the question

A result you can’t verify isn’t finished. PMT is PV run backwards, so it must give back the payment:

E12  =PMT(E4/12,E5*12,-E11)
E13  =E10+E11
E14  =E7-E13

E12 shows $4,500.00: the loan is right. Savings plus loan come to $777,807.51, and the fit-out costs $850,000, so the plan is $72,192.49 short.

Thinking like an analyst

The answer to Robert is not “no”, it’s “not quite, and here is what closes the gap”. Because every assumption is an input cell, you can test the levers in seconds: change the share in E1, the payment in E6 or the term in E5, and E14 updates. That is the advantage of the formula route over Goal Seek, which would leave a frozen number in E11.

Say the assumptions out loud in the note: a steady 4% return on savings, revenue holding at 2025’s average for five years, and a 6.5% rate that a lender would offer in five years’ time. Each is a guess, and the board should see them.

Common mistakes

• Annual rates with monthly payments in FV, PV or PMT.
• Dropping the minus on the payment, so E10 or E11 comes out negative and the total funding turns into a subtraction.
• Basing the saving on one month (B13, December) instead of the average month.
• Running Goal Seek with the formula cell as the “by changing” cell: it must be the typed input.