4.1 IRR Financial Function

Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

IRR(values, [guess])

= IRR (A2:A6, 0.1)
= 15%

4.2 XIRR: Financial Function

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.

XIRR(values, dates, [guess])

Values = Positive or negative cash flows (an array of values)
Dates = Specific dates (an array of dates)
[Guess] = An assumption of what you think IRR should be

= XIRR (B2:B6, C2:C6, 0.1)
= 24%

4.3 NPER: Financial Function

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

  NPER(rate,pmt,pv,[fv],[type])

  Rate = It is the interest rate/period
  PMT = Amount paid per period
  PV = Present Value
  [FV] = An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)
  [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)

₹200 is paid per year for a loan of ₹1000. The interest rate is 10% p.a. and the payment needs to be done yearly. Find out the NPER.

Solution: We need to calculate NPER in the following manner

= NPER (10%, -200, 1000)
= 7.27 years

4.4 Future Value (FV): Financial Function

FV, calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump sum payment.

  FV(rate,nper,pmt,[pv],[type])

  Rate = It is the interest rate/period
  Nper = Number of periods
  [Pmt] = Payment/period
  PV = Present Value
  [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)
  

A has invested the ₹100 in 2018. The payment has been made yearly. The interest rate is 10% p.a. What would be the FV in 2021?

Solution: In excel, we will put the equation as follows

= FV (10%, 3, 1, – 100)
= ₹129.79

4.5 Present Value (PV): Financial Function

PV, one of the financial functions, calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments (such as a mortgage or other loan), or a future value that's your investment goal.

PV = (Rate, Nper, [Pmt], FV, [Type])

Rate = It is the interest rate/period
Nper = Number of periods
[Pmt] = Payment/period
FV = Future Value
[Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)

The future value of an investment in the ₹100 in 2019. The payment has been made yearly. The interest rate is 10% p.a. What would be the PV as of now?

Solution: In excel, we will put the equation as follows

= PV (10%, 3, 1, – 100)
= ₹ 72.64

4.6 Net Present Value (NPV): Financial Function

Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).

NPV = (Rate, Value 1, [Value 2], [Value 3]…)

Rate = Discount rate for a period
Value 1, [Value 2], [Value 3]… = Positive or negative cash flows
Here, negative values would be considered as payments, and positive values would be treated as inflows.

Here is a series of data from which we need to find NPV.

Solution: In excel, we will put the equation as follows

=NPV (5%, B4:B7) + B3
= ₹ 240.87