Financial FunctionsBeginner

Work Out How Much a Monthly Budget Can Borrow with PV

A lender quotes a price and asks what the payment will be. A small business usually starts from the other end: this is what we can pay each month, so how big a loan is that? PV answers that question directly, without guessing loan amounts into PMT until one fits.

Task:

You do the books for a family bakery that wants a refrigerated delivery van. After going through the cash flow, the owners can commit $850 a month to loan repayments, and the bank has offered 7.2% a year over 5 years, repaid monthly. In B5, use PV to work out the largest loan that $850 a month can repay. In B6, work out how much of everything the bakery pays back over the 5 years is interest.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

6 rows × 2 columns2 cells you fill in
AB
1ItemValue
2Monthly payment the bakery can afford850
3Annual interest rate0.072
4Loan term (years)5
5Largest loan the budget covers
6Total interest over the loan
What this exercise teachesMay contain the answer

PV asks what single amount today is worth the same as a run of equal payments, and for a loan that amount is exactly what the bank will lend against them. B3/12 turns 7.2% a year into 0.6% a month and B4*12 turns 5 years into 60 payments, so all three arguments describe the same month; leave the rate annual and PV discounts each payment as if a whole year passed between them, which shrinks the loan to a fraction of what the bank would really offer. The payment goes in as -B2 because it leaves the bakery, and PV reports the loan as the opposite of the payments, so a negative payment gives a positive $42,722.81. That is well short of the $51,000 that 60 payments of $850 add up to, and the gap is the point of B6: B2*B4*12 is the $51,000 the bakery will actually pay, and taking away the $42,722.81 it borrowed leaves $8,277.19 of interest. Knowing the ceiling before visiting a dealer turns the question from "can we afford this van?" into "which vans cost under $42,700?", which is a far better conversation to have with a salesperson.