Topic 15

Financial Functions

Learn how to perform standard loan calculations and savings projections in Excel. We will master five core financial functions — PMT, PV, FV, NPER, and RATE — and learn how to align rates and terms to match payment cycles.

5 functions covered Practice file: PMT.xlsx

Topic Practice File

Download PMT.xlsx

Step 1 — The 5 Core Financial Functions

Excel provides a family of financial functions that solve different parts of a standard interest calculation. If you know any four variables, you can calculate the fifth missing variable:

PMT, PV, FV, NPER, and RATE functions overview

📌

The Five Variables

• PMT (Payment): The recurring amount paid in each period.
• PV (Present Value): The initial starting amount (loan value or deposit).
• FV (Future Value): The target ending balance (savings value or $0 for paid-off loans).
• NPER (Periods): The total number of payment periods (loan term).
• RATE: The interest rate per payment period.

Step 2 — The PMT Cycle & Sign Convention

To get correct results, you must respect two critical rules when writing financial formulas:

⚠️

Rule 1: Time Cycle Alignment (Monthly Payments)

The interest rate and periods arguments must match the frequency of the payments. Because interest rates are usually quoted **annually** and terms are quoted in **years**, you must convert them if payments are **monthly**:
• **Rate**: Divide the annual rate by 12 (e.g. AnnualRate / 12).
• **Nper**: Multiply the term in years by 12 (e.g. Years * 12).

💡

Rule 2: Cash Flow Sign Convention (+ vs -)

Excel tracks cash movements relative to your pocket/account:
• **PV (Loan)** is a **positive value** because the bank credits money *into* your account.
• **PMT (Installment)** is a **negative value** because it is a cash outflow *from* your account to pay off debt.
• If you write `=PMT(...)`, the result will display as a negative number. If you need a positive result, prefix the function or the PV argument with a negative sign (e.g. =-PMT(...) or PMT(rate, nper, -pv)).

Step 3 — Loan Calculators: 4 Scenarios

Open PMT.xlsx. The sheet displays four loan scenarios side-by-side (Columns D, G, J, and M) with one variable missing in each:

Scenario 1: Find Installment (Cell E8)
Inputs: Loan = $50,000, Years = 5, Annual Rate = 6%. Calculate the monthly payment:

= PMT(E7 / 12, E6 * 12, E5)

This returns -$966.64.

Scenario 2: Find Loan Amount (Cell H5)
Inputs: Years = 5, Annual Rate = 6%, Installment = -$966.64. Calculate how much you can borrow:

= PV(H7 / 12, H6 * 12, H8)

This returns $50,000.00.

Scenario 3: Find Loan Term (Cell K6)
Inputs: Loan = $50,000, Annual Rate = 6%, Installment = -$966.64. Calculate the term in **years**:

= NPER(K7 / 12, K8, K5) / 12

Note: NPER returns periods in months. We divide the result by 12 to display it in **years** (5.0).

Scenario 4: Find Interest Rate (Cell N7)
Inputs: Loan = $50,000, Years = 5, Installment = -$966.64. Calculate the **annual** interest rate:

= RATE(N6 * 12, N8, N5) * 12

Note: RATE returns the monthly rate. We multiply by 12 to convert it to an **annual** interest rate (6.0%).

Step 4 — Future Savings Projections (FV)

Financial functions can also calculate future wealth rather than loans. Look at **Row 13 to 17** in Column E:

Scenario 5: Future Savings Value (Cell E17)
Inputs: Monthly saving = -$300 (cash outflow), Years = 10, Interest Rate = 10% (annual). Find the final accumulated amount:

= FV(E16 / 12, E15 * 12, E14)

This returns $61,452.92. Note that because your monthly deposit is entered as a negative number, the final accumulated amount is returned as positive.

Self-Check — Verify Your Formulas

Type the formulas into PMT.xlsx as described above, then input your calculated numbers below to verify they match.

Financial Functions Formula Verification Input raw values (percentage as 6% or 0.06), tolerance: +/-0.02

Summary Location / Task	Your Value
Cell E8 (Monthly installment)
Cell H5 (Loan Amount / PV)
Cell K6 (Loan Term in Years)
Cell N7 (Annual Interest Rate)
Cell E17 (Accumulated Savings amount)

Lab · Charts & Dashboards

Project 01 — Party Supply