BSB107 Chap.6 Borrowing: Loan Payments and Repayment Schedules
Borrowing: Loan Payments and Repayment Schedules
The same mathematics, the other way round
Topic 5 treats a loan as an investment made by the lender: the amount borrowed is the present value and the balance at the end is zero. Regular repayments bring in the PMT and NPER functions, and CUMIPMT and CUMPRINC split the payments into interest and principal.
Schedules and choices
A repayment schedule shows interest falling as the balance falls.
Changing the term or capping the payment shows the trade-off between monthly cost and total interest.
The portfolio's Week 5 sheet
The Week 5 sheet is the one you solve live in the presentation video, explaining what you do as you go.
It asks for a repayment schedule and specific loan formulas in the answer boxes, and its hints repeat the Week 4 checks on rate, period and signs, adding that a loan with no balloon payment must end at a zero balance.
Payday loans, the subject of the next chapter, show what happens when this arithmetic is pushed to extreme rates and very short terms.
What this chapter covers
- 01
Borrowing as the converse of investing
- 02
PMT for the regular repayment
- 03
NPER for the length of a loan
- 04
CUMIPMT and CUMPRINC
- 05
Building a repayment schedule
- 06
Secured loans, credit cards, overdrafts and BNPL
- 07
APR and credit scores
Worked example · free
Where the first and last years of a car loan go
- +1rate = 0.072 ÷ 12 = 0.006; nper = 60; =−PMT(0.006, 60, 24000) = $477.50 a month, about $5,729.96 a year.
- +1Year 1 interest: =−CUMIPMT(0.006, 60, 24000, 1, 12, 0) = $1,593.26.
- +1Year 1 principal = 5,729.96 − 1,593.26 = $4,136.70.
- +1Year 5: interest $217.35 and principal $5,512.61, because the balance charged interest is now small.
Key terms
- Secured loan
- A loan formally linked to an asset that the lender can take if the borrower defaults.
- Collateral
- Assets pledged to a lender as security for a loan.
- Annual percentage rate
- A loan rate that includes all interest and fees, so different loans can be compared.
- Credit score
- An independent rating built from your repayment record that signals to lenders how reliably you meet your debts.
- Hire purchase
- Buying an asset on credit where ownership passes to the buyer only once the final instalment is paid.
Borrowing: Loan Payments and Repayment Schedules FAQ
Why does the interest part of each repayment fall over time?
Interest each month is charged only on the balance still owing. As repayments reduce that balance, the interest charge shrinks and more of each fixed repayment goes to principal, which is why the vocabulary sheet says the interest charge decreases as a loan is paid off.
My schedule ends with a negative balance. What went wrong?
The usual causes are the wrong number of periods or a rounded repayment. A loan with no balloon payment should finish at exactly zero, so keep the payment unrounded and check that nper matches the term in months.
When would I use NPER instead of PMT?
Use PMT when the term is fixed and you need the repayment. Use NPER when the repayment is fixed, for example capped at what you can afford, and you need to know how many periods the loan will last.
What is the difference between a secured loan and a credit card?
A secured loan is tied to an asset, a home in the case of a mortgage, and the lender can take that asset if you default. A credit card is a short-term facility that charges high interest if the balance is not paid within four to six weeks.
How can I see what a shorter loan term would cost each month?
Change nper in the PMT function and read the new repayment, then compare the total paid with the original loan. Shortening a term raises each payment but lowers the total interest, because the balance is outstanding for less time.
What does CUMIPMT tell me that PMT does not?
PMT gives the fixed regular repayment. CUMIPMT adds up the interest paid between two chosen periods, such as the first twelve months, and CUMPRINC does the same for principal, so together they show how each year's repayments split.
Exam move
Construct one amortisation schedule from blank cells with interest, principal and closing balance columns, confirming a zero finish. Use it to test three scenarios: a shorter term through PMT, an affordability cap through NPER, and first-year interest through CUMIPMT. Narrate the Week 5 problem aloud as if recording.
Working through Borrowing: Loan Payments and Repayment Schedules in BSB107? Sia is AskSia’s AI Finance tutor — ask any BSB107 Borrowing: Loan Payments and Repayment Schedules question and get a clear, step-by-step explanation grounded in how BSB107 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
