# Excel Made Easy

## Financial functions: effect, irr, mirr, nominal, nper, ppmt

#### Excel Function EFFECT

The EFFECT() function calculates the effective interest rate for a specified nominal rate of interest.

This also takes into account the number of compounding periods per year, which must be a positive integer.

This function is most helpful in comparing financial loan with various compound rates.

The parameters for this function are as follows:

nominal interest; no. of periods.

 Nominal interest No. of periods per year EFFECT Formula 0.1 4 0.103812890625 =EFFECT(L5,M5) 0.125 3 0.130280671296297 =EFFECT(L6,M6) 0.0975 5 0.101377374519819 =EFFECT(L7,M7) 0.08 2 8.16000000000001E-02 =EFFECT(L8,M8)

#### Excel Function IRR

The IRR() function is used to calculate the internal rate of return for a series of cash flows.

It requires that the cash flow must have a constant frequency interval.

The amount of flow can vary at each flow.

The formula used for calculating IRR is as given below:

 Year Income IRR() Formula Remarks Initial Investment -5000 Initial investment is always negative Year 1 220 -0.956 =IRR(M17:M18) IRR after year1 Year 2 275 -0.742449580768787 =IRR(M17:M19) IRR after year2 Year 3 2000 -0.222880549204708 =IRR(M17:M20) IRR after year3 Year 4 3000 2.80896585256853E-02 =IRR(M17:M21) IRR after year 4

#### Excel Function MIRR

The MIRR() function returns the modified internal rate of return for the supplied cash flows.

The Cash flow must be periodic.

The MIRR is often related to prediction of profitability of an investment.

The function takes following parameters:

values representing cash flows; financial rate; reinvestment rate.

The difference with IRR is that, this function takes into account, the interest on reinvested cash also.

 Year Income MIRR() Formula Remarks Initial Investment -5000 Initial investment is always negative Year 1 220 -0.956 =MIRR(M33:M34,5%,6%) MIRR after year1 Year 2 275 -0.681189711583832 =MIRR(M33:M35,5%,6%) MIRR after year2 Year 3 2000 -0.202225770156307 =MIRR(M33:M36,5%,6%) MIRR after year3 Year 4 3000 3.28919751065269E-02 =MIRR(M33:M37,5%,6%) MIRR after year 4

#### Excel Function NOMINAL

The NOMINAL() function is used to calculate the nominal interest based on a specified effective interest rate.

This function also takes into account, the number of compounding periods per year.

 Effective Interest No. of periods NOMINAL Formula 0.1 4 9.64547563377804E-02 =NOMINAL(L48,M48) 0.15 5 0.141733610501068 =NOMINAL(L49,M49) 0.2 4 0.186540557568422 =NOMINAL(L50,M50) 0.25 3 0.231652035047826 =NOMINAL(L51,M51) 0.3 5 0.269369760308917 =NOMINAL(L52,M52)

#### Excel Function NPER

The NPER() function is used to determine the number of periods required to repay a loan.

This takes into account, a constant periodic payment and an interest rate.

This function takes in the following parameters:

interest rate; amount per period; present value; future value [optional]; type [0 or 1].

 Interest NPER Formula 0.04 10.3380350715077 =NPER(L62,-12000,100000) 0.05 11.0472368746482 =NPER(L63,-12000,100000) 0.07 12.9394949071843 =NPER(L64,-12000,100000) 0.06 11.8956610459419 =NPER(L65,-12000,100000) 0.08 14.2749145860032 =NPER(L66,-12000,100000)

#### Excel Function PPMT

The PPMT() function is used to calculate the principal payment during a period of loan or investment.

This function requires a constant payment period and a constant rate of interest.

This function takes in the following parameters:

interest rate; principal payment period; loan repayment period; present loan value; future value[optional]

payment type[optional - 0 or 1]

Interest Rate: 4%

Loan Amount: \$100000

 Month PPMT Formula 1 -1508.3188721933 =PPMT(4%/12,L74,60,100000) 2 -1513.34660176728 =PPMT(4%/12,L75,60,100000) 3 -1518.39109043984 =PPMT(4%/12,L76,60,100000) 4 -1523.45239407464 =PPMT(4%/12,L77,60,100000) 5 -1528.53056872155 =PPMT(4%/12,L78,60,100000)