Financial modelling
functions
PV Function
• The PV Function is a widely used financial function in Microsoft Excel. It
calculates the present value of a loan or an investment.
• In financial statement analysis, PV is used to calculate the dollar value of
future payments in the present time. For multiple payments, we assume
periodic, fixed payments and a fixed interest rate. Alternatively, the
function can also be used to calculate the present value of a single
future value.
Formula
• =PV(rate, nper, pmt, [fv], [type])
• rate (required argument) – The interest rate per compounding period.
A loan with a 12% annual interest rate and monthly required
payments would have a monthly interest rate of 12%/12 or 1%.
Therefore, the rate would be 1%.
• nper (required argument) – The number of payment periods. For
example, a 3 year loan with monthly payments would have 36
periods. Therefore, nper would be 36 months.
• pmt (required argument) – The fixed payment per period.
• fv (optional argument) – An investment’s future value at the end of all
payment periods (nper). If there is no input for fv, Excel will assume
the input is 0.
• type (optional argument) – Type indicates when payments are issued.
There are only two inputs, 0 and 1. If type is omitted or 0 is the input,
payments are made at period end. If set to 1, payments are made at
period beginning.
Example
• You will receive ₹5,000 at the end of each year for 4 years, and the
interest rate is 10% per year. Find the present value (PV).
• =PV(0.10, 4, 5000)
• Result = -₹15,813.72
• The negative sign indicates a cash outflow (you are investing
₹15,813.72 today to receive ₹5,000 per year for 4 years at 10%
interest).
Example 2
• You will receive ₹10,000 after 4 years at an interest rate of 10%.
Find its present value.
• =PV(0.10, 4, 0, 10000)
• = -₹6,830.13
• ₹6,830.13 today is equal to ₹10,000 received after 4 years at 10%
interest.
FV function
• The FV function in Excel is a financial function used to calculate
the future value of an investment or loan based on a constant
interest rate, a fixed number of periods, and constant periodic
payments.
• =FV(rate, nper, pmt, [pv], [type])
[Link] (required) – Interest rate per period.
•If annual rate is given but payments are monthly, divide by 12.
[Link] (required) – Total number of payment periods.
•For a 5-year loan with monthly payments: 5*12.
[Link] (required) – Payment made each period (negative for outflow, positive
for inflow).
•If omitted, you must provide pv.
[Link] (optional) – Present value (the lump sum at the start).
•Default is 0.
[Link] (optional) – When payments are due:
•0 = End of period (default)
•1 = Beginning of period
Example
• You invest ₹1,000 every month for 5 years at an annual interest rate
of 12% (which is 1% per month).
• =FV(0.01, 60, -1000)
• = ₹81,670.00 (approximately)
• If you invest ₹1,000 at the end of each month for 5 years at 12%
annual interest, you’ll have about ₹81,670 at the end.
Example 2
• You invest ₹10,000 today at 10% annual interest for 4 years.
• =FV(0.10, 4, 0, -10000)
• = ₹14,641.00
• ₹10,000 invested today grows to ₹14,641 after 4 years at 10% annual
interest.
Example 3
• You deposit ₹5,000 every year for 4 years and also invest an initial
amount of ₹10,000. The interest rate is 10%.
• =FV(0.10, 4, -5000, -10000)
• = ₹36,405.10
• After 4 years, your total future value will be ₹36,405.10.
NPV and IRR
• Already done in capital budgeting (investment decisions)
PMT function
• The PMT function in Excel is used to calculate the payment amount
for a loan or investment based on a constant interest rate, a fixed
number of periods, and equal payments.
• =PMT(rate, nper, pv, [fv], [type])
• rate (required) – Interest rate per period.
• If annual interest rate is given but payments are monthly, divide by 12.
• nper (required) – Total number of payment periods.
• For a 5-year loan with monthly payments: 5*12.
• pv (required) – Present value (loan amount or investment principal).
• fv (optional) – Future value after the last payment. Default is 0.
• type (optional) – When payments are due:
• 0 = End of period (default)
• 1 = Beginning of period
Example 1
• You take a loan of ₹500,000 (5 lakh) for 5 years at an annual interest rate of 10%, with
payments made monthly.
• Convert annual rate to monthly → 10% ÷ 12 = 0.00833
Convert years to months → 5 × 12 = 60 months
• =PMT(0.10/12, 60, 500000)
• = -₹10,624.00
• You must pay ₹10,624 per month for 5 years to repay a ₹5 lakh loan at 10% annual
interest.
Example 2: Annual Payment for a
Loan
• Loan amount = ₹100,000
Interest rate = 8% per year
Loan term = 4 years
• =PMT(0.08, 4, 100000)
• = -₹32,973.00
• You need to deposit ₹32,973 each year for 5 years to reach ₹200,000 at 10%
interest.
IPMT Function
• The IPMT function in Excel calculates the interest payment for a specif
ic period of an investment or loan based on constant periodic paymen
ts and a constant interest rate.
• = IPMT(rate, per, nper, pv, [fv], [type])
• rate: The interest rate for each period (required).
• per: The specific period for which you want to find the interest (require
d). This must be between 1 and nper.
• nper: The total number of payment periods in an annuity (required).
• pv: The present value, or the total amount that a series of future payme
nts is worth now (required).
• fv: The future value, or a cash balance you want to attain after the last p
ayment (optional; defaults to 0).
• type: Indicates when payments are due. Use 0 for the end of the period
(default) and 1 for the beginning (optional).