Skip to content
Educora
EN
AzərbaycancaEnglishРусскийTürkçe
My learningEducora StudioSettings
University25 min22 / 27

Financial functions: loans, savings and investments

The maths behind PMT, IPMT, PPMT, FV, PV, NPV and IRR, the sign convention, a loan amortisation schedule built with formulas and worked examples in manat.

Check yourself
In this lesson you will learn
  • Derive the annuity payment formula and calculate it with PMT
  • Build an amortisation schedule with IPMT and PPMT
  • Calculate the time value of money with FV and PV
  • Evaluate an investment project with NPV and IRR

Murad wants a ₼20,000 car loan: 12% a year for 36 months. What will the monthly payment be? How much interest does the bank earn in total? Meanwhile Aysel saves ₼200 a month and Elvin is thinking about investing ₼10,000 in a project. In this lesson you will learn the functions banks and finance departments use every day — together with the maths behind them.

The annuity payment: PMT

An annuity is a payment of the same amount every period. For the bank, the loan equals the present value of all future payments: P = A/(1 + r) + A/(1 + r)² + … + A/(1 + r)ⁿ. This is a geometric series whose sum is A · (1 − (1 + r)⁻ⁿ) / r. Solving for A gives the formula below.

A = P · r / (1 − (1 + r)⁻ⁿ)A = P · r / (1 − (1 + r)⁻ⁿ)
where:
  • Athe payment per period, ₼ (PMT in Excel)
  • Pthe loan amount (present value), ₼
  • rthe interest rate per period: 12% a year with monthly payments is 12% / 12 = 1% = 0.01
  • nthe number of periods (months)

Excel: =PMT(rate, nper, pv, [fv], [type]); type = 1 if payments are made at the start of each period.

Murad's car loan

P = ₼20,000, 12% a year, a 36-month annuity. Find the monthly payment, the total repaid and the total interest.

Show solution
r = 0.12 / 12 = 0.01; n = 36.
(1.01)⁻³⁶ ≈ 0.69892, so 1 − 0.69892 = 0.30108.
A = 20,000 · 0.01 / 0.30108 ≈ ₼664.29.
Excel: =PMT(12%/12,36,20000) → −664.29 (negative, because the money leaves your pocket).
Total repaid: 664.29 · 36 = ₼23,914.44; interest: 23,914.44 − 20,000 = ₼3,914.44.

The amortisation schedule: IPMT and PPMT

Iₖ = Bₖ₋₁ · r; Pₖ = A − Iₖ; Bₖ = Bₖ₋₁ − Pₖ
where:
  • Iₖthe interest part of payment k, ₼ (IPMT)
  • Pₖthe principal part of payment k, ₼ (PPMT)
  • Bₖthe balance after payment k, ₼ (B₀ = P)

Excel: =IPMT(rate, per, nper, pv) and =PPMT(rate, per, nper, pv); every month IPMT + PPMT = PMT.

The first and the last month

In Murad's loan, how are the 1st and the 36th payments split between interest and principal?

Show solution
Month 1: I₁ = 20,000 · 0.01 = ₼200.00; P₁ = 664.29 − 200 = ₼464.29; B₁ = ₼19,535.71.
Excel: =IPMT(1%,1,36,20000) → −200.00; =PPMT(1%,1,36,20000) → −464.29.
Month 36: =IPMT(1%,36,36,20000) → −6.58; =PPMT(1%,36,36,20000) → −657.71.
Conclusion: at first most of the payment goes to interest, at the end almost all of it repays the debt. That is why early repayment saves more interest in the first months.
Interactive
Loading simulation…
Annuity amortisation schedule for a ₼20,000 loan (first 8 months).

Time value of money: FV and PV

FV = A · ((1 + r)ⁿ − 1) / rFV = A · ((1 + r)ⁿ − 1) / r
where:
  • FVthe amount accumulated after n periods, ₼
  • Athe amount deposited at the end of each period, ₼

Excel: =FV(rate, nper, pmt, [pv], [type]), and the reverse — today's value of future flows: =PV(rate, nper, pmt, [fv], [type]).

Aysel's savings and PV

1) For 5 years Aysel puts ₼200 at the end of every month into a deposit at 6% a year, compounded monthly. How much will she have? 2) What is a contract paying ₼1000 a year for 10 years worth today at 8%?

Show solution
1) r = 0.5% = 0.005, n = 60: (1.005)⁶⁰ ≈ 1.34885.
FV = 200 · 0.34885 / 0.005 ≈ ₼13,954.01; =FV(6%/12,60,-200) → 13,954.01.
She deposits ₼12,000 herself and earns ₼1,954.01 in interest.
2) =PV(8%,10,-1000) → ₼6,710.08: the future ₼10,000 is worth only ₼6,710.08 today, because money today can earn interest.

Investment decisions: NPV and IRR

NPV = −C₀ + ∑ₜ₌₁ⁿ Cₜ / (1 + r)ᵗ; IRR: NPV(IRR) = 0NPV = −C₀ + ∑ₜ₌₁ⁿ Cₜ / (1 + r)ᵗ; IRR: NPV(IRR) = 0
where:
  • C₀the initial investment (t = 0), ₼
  • Cₜthe cash flow at the end of year t, ₼
  • rthe discount rate (cost of capital)

NPV > 0 means the project earns more than the cost of capital. IRR is the rate that makes NPV zero; accept the project when IRR > r.

Elvin's project

B1 holds today's investment of −₼10,000 and B2:B6 the income for years 1–5: 3000, 3500, 4000, 2500, 2000 ₼. The cost of capital is 10%. Find NPV and IRR. Is the project worth it?

Show solution
Discounted flows: 3000/1.1 = 2727.27; 3500/1.1² = 2892.56; 4000/1.1³ = 3005.26; 2500/1.1⁴ = 1707.53; 2000/1.1⁵ = 1241.84. Sum ≈ ₼11,574.47.
NPV = 11,574.47 − 10,000 = ₼1,574.47.
Excel: =NPV(10%,B2:B6)+B1 → 1,574.47.
IRR: =IRR(B1:B6) → ≈ 16.38% > 10%.
Both criteria support the project.
Currency format (two decimals)Ctrl+Shift+$
Percentage formatCtrl+Shift+%
Lock the rate and payment cells with $ in the scheduleF4

Key points

  • Annuity: A = P · r / (1 − (1 + r)⁻ⁿ); in Excel =PMT(rate, nper, pv).
  • Rate and periods must use the same unit: monthly means rate/12 and years*12.
  • Sign convention: money paid out is negative, money received is positive.
  • IPMT + PPMT = PMT; in the early months most of the payment is interest.
  • Net present value: =NPV(r, C₁:Cₙ) + C₀; IRR is the rate that makes NPV zero.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10
A ₼10,000 loan at 24% a year for 12 months. Which formula gives the monthly payment correctly?