Challenge Scenario
In business accounting and personal finance, companies regularly take loans to purchase assets like vehicles, servers, and office fitouts. When taking a loan, finance managers need to compute two essential numbers: the fixed Monthly Payment (EMI), and the Total Interest Expense paid over the entire life of the loan.
As a Corporate Finance Analyst, you are auditing four active business financing facilities:
- Monthly Payment (Column E): Calculate the fixed monthly installment for each loan using the standard formula
=PMT(B2/12, C2*12, -D2). Divide annual interest rate by 12, multiply loan years by 12, and enter principal as negative so PMT returns a positive value.
- Total Interest Paid (Column F): Multiply the monthly payment by total loan months, then subtract the original principal:
=(E2*C2*12)-D2.
- Total Monthly Outflow (Cell E7): Sum all four monthly loan commitments using
SUM(E2:E5).
- Total Lifetime Interest (Cell F7): Sum the total interest costs across all four loans using
SUM(F2:F5).
Your Sheet Layout
Here is how the loan amortization schedule is organized:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:D5 |
Loan Terms |
Input Parameters |
Principal, annual rate %, and tenure in years |
Read-only financing terms. |
E2:E5 |
Monthly Payment |
Installment Zone |
=PMT(B2/12, C2*12, -D2) |
Calculates monthly repayment installment. |
F2:F5 |
Total Interest |
Finance Cost Zone |
=(E2*C2*12)-D2 |
Total interest paid over the full tenure. |
E7 |
Total Monthly Payment |
Summary |
=SUM(E2:E5) |
Combined monthly cash outflow ($2,018.76). |
F7 |
Total Interest Paid |
Summary |
=SUM(F2:F5) |
Combined borrowing cost ($12,214.97). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells E2:E5, calculate the monthly repayment installment using PMT.
2
In cells F2:F5, calculate the total lifetime interest paid for each loan.
3
In cell E7, sum the total monthly payment obligations across all loans.
4
In cell F7, sum the total lifetime interest costs across the loan portfolio.
Solution & Formula Breakdown
Calculating loan installments accurately ensures your business maintains healthy cash flow:
1. Monthly Repayment Installment with PMT (E2:E5)
=PMT(B2/12, C2*12, -D2)
The PMT(rate, nper, pv) function takes three key arguments:
B2/12 (Rate): The annual rate divided by 12 months (e.g. 6.0% / 12 = 0.5% per month).C2*12 (Nper): The total number of monthly payment periods (e.g. 3 years × 12 = 36 months).-D2 (PV): The principal loan balance entered as a negative number so PMT outputs a positive cash value.- Row 2 (Office Fitout): $15,000 at 6% for 3 years → $456.33/month.
- Row 3 (Delivery Van): $30,000 at 7.5% for 5 years → $601.14/month.
- Row 4 (Server Hardware): $8,000 at 5% for 2 years → $350.97/month.
- Row 5 (CNC Tooling): $25,000 at 8% for 4 years → $610.32/month.
2. Calculating Total Lifetime Interest (F2:F5)
=(E2*C2*12)-D2
Total payments minus the borrowed principal gives the net interest cost. For Row 2: ($456.33 × 36) - $15,000 = $1,427.85.
3. Portfolio Cashflow Summaries (E7 & F7)
- Cell E7:
=SUM(E2:E5) → $2,018.76 monthly total.
- Cell F7:
=SUM(F2:F5) → $12,214.97 total interest.
If your PMT formula returns a red negative number in parentheses, add a minus sign in front of the loan principal (-D2) to convert it to a positive cash outflow!