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) |