Scribd
Upload
5 views2 pages

Excel Formulas

The document provides formulas for calculating present value (PV) and future value (FV) for lump sums and ordinary annuities using Excel and theoretical approaches. It also covers nominal and effective interest rates, loan amortization calculations including payment, principal, and interest, as well as methods for calculating net present value (NPV) and internal rate of return (IRR). Each section includes both Excel formulas and theoretical formulas for clarity.

Uploaded by

celiamansourii1340
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views2 pages

Excel Formulas

The document provides formulas for calculating present value (PV) and future value (FV) for lump sums and ordinary annuities using Excel and theoretical approaches. It also covers nominal and effective interest rates, loan amortization calculations including payment, principal, and interest, as well as methods for calculating net present value (NPV) and internal rate of return (IRR). Each section includes both Excel formulas and theoretical formulas for clarity.

Uploaded by

celiamansourii1340
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1.

Present Value
1.1. Lump Sum

• Excel Formula : =PV(rate, nper, pmt, -FV) with pmt = 0


• Theoretical formula: 𝑃𝑉 = 𝐹𝑉 ∗ (1 + 𝑖)−𝑛

1.2. Ordinary Annuity

• Excel Formula : = PV(rate, nper, -pmt)


1−(1+𝑖)−𝑛
• Theoretical formula: 𝑃𝑉 = 𝑃𝑀𝑇 ∗

i
𝑖

ar
2. Future Value
2.1. Lump Sum

• Excel Formula : =FV(rate, nper, pmt, -PV) with pmt = 0


• Theoretical formula: 𝐹𝑉 = 𝑃𝑉 ∗ (1 + 𝑖)𝑛

kt
2.2. Ordinary Annuity

• Excel Formula : = FV(rate, nper, -pmt)


(1+𝑖)𝑛 −1
• Theoretical formula: 𝐹𝑉 = 𝑃𝑀𝑇 ∗ 𝑖
m
3. Nominal & Effective rates
3.1. Effective rate (EAR)

• Excel formula: EFFECT(nominal_rate, npery)


𝑖
si
• Theoretical formula: (1 + )𝑀 − 1 with M is the frequency
𝑀

3.2. Nominal rate

•
as

Excel Formula: =NOMINAL(effective_rate, npery)


• Theoretical formula:
Step 1- determine the periodic rate
Step 2- multiply it by M

4. Loan Amortization
4.1. PMT
w

• Excel formula: =PMT(rate, nper, -pv)


𝐿0
• Theoretical formula: 1−(1+𝑖)−𝑛
𝑖

4.2. Principal

• Excel formula: =PPMT(rate, per, nper, -pv) with “per” is the period on which we want to determine the
principal
• Theoretical formula:
Step1: determine 𝑃1
Step2: determine 𝑃𝐾 Using 𝑃𝐾 = 𝑃𝑗 ∗ (1 + 𝑖)𝑘−𝑗

4.3. Interest

• Excel formula: =IPMT(rate, per, nper, -pv) with “per” is the period on which we want to determine the interest
• Theoretical formula: beginning balance * Interest rate or PMT – 𝑃𝐾

5. IRR and NPV

i
5.1. NPV

ar
• Excel formula: =NPV(discount rate, cash_inflows) + initial_investment (initial investment < 0)
• Theoretical formula: 𝐼0 + ∑𝑛1 𝑐𝑎𝑠ℎ 𝑖𝑛𝑓𝑙𝑜𝑤𝑡

5.2. IRR

kt
• Excel formula : =IRR(all cash flows)
• Theoretical formula:
Step 1- trial and error
Step 2- linear interpolation
m
si
as
w

You might also like