# Excel Calculation & Its Meaning IV

Excel Calculation & Its Meaning IV

## Introduction

In our previous series of Financial Calculation through MS-Excel tools , we covered

·        DISC

·        DOLLARDE

·        DOLLARFR

·        DURATION

·        EFFECT

·        FV

·        FVSCHEDULE

Now , In this Post Excel Calculation & Its Meaning IV We will cover some financial terms like

·       INTRATE

·       IRR

·       ISPMT

·       MDURATION

·       MIRR

·       NOMINAL

·       NPER

·       NPV

23)

INTRATE—It is also called Interest Rate. It basically calculates the interest rate of fully invested security (Shares or bond)

Syntax :–

=INTRATE(Settlement, maturity, investment, redemption,[basis])

·        Settlement (required argument) – This is the settlement date of the security. The security settlement date is the date after the issue date when the security is traded to the buyer.

·        Maturity (required argument) The security’s maturity date. The maturity date is the date when the security is matured.

·        Investment (required argument) – The amount that is invested in the security.

·        Redemption (required argument) – This is the amount we will receive at maturity.

·        Basis (optional argument)  The type of day count basis to be used. The possible values of the basis are: 0 – 30/360 (US/NASD) ; 1—Actual/Actual ; 2—Actual/360 ; 3—Actual/365 (India) ; 4-30/360 (European)

Lets calculate it through using INTRATE  function as well as Formula

 Settlement date Jan -01-2017 Maturity date Dec-31-2017 Investment done 100000 Redemption 102000 =INTRATE =INTRATE(Jan-01-2017,De-31-2017,100000,102000,3)= 0.020054945 =0.020054945*100= 2%

Mathematical Calculation

INTRATE=      Redemption –(minus) Investment     *     B

Investment                                      DIM

B=number of days in a year, depending on the year basis

DIM= number of days from settlement to maturity

INTRATE  =

Redemption –(minus) Investment

Investment

=     102000—100000   = 2000/100000  = 0.02  ——step1

B   (2017 has 365 days)                         = 365/365 =1  ————————- step 2

DIM (Jan 01 2017 – Dec 31, 2017 has 365 days)

INTRATE  = STEP1*STEP2 =  0.02*1 = 0.02  (2%)

We can also check in other ways

 Present Value 100000 Interest Rate 2% Annum (2%/12= Monthly Interest Rate) Terms 12 Months Monthly Interest 100000*2%/12 = 166.67 Annual Interest 166.67*12 = 2000 Total Value 102000 (which is same as given in Redemption value, so my INTRATE is correct)

24)

IRR:– It is also called Internal Rate of Return. This function return (give) the Internal rate of return for a given cash flow ,that is, the initial investment value and a series of net income values.

This function is very helpful in financial modeling. It is frequently used by businesses to compare and decide between capital projects. For eg: when a company have two opportunities: one to invest in a new factory and the second is to expand its existing factory. Using the IRR function, we can estimate the IRRs for both scenarios and check which one is higher than the weighted average cost of capital (WACC) of the business (also known as a hurdle rate ). If both are greater than the cost of capital, we will choose the one that shows a higher IRR and/or Net Present Value (NPV) than the other.

Syntax :–

=IRR(values,[guess])

·        Values (required argument) – This is an array of values that represent the series of cash flows. Cash flows include investment and net income values.

·        [Guess] (optional argument) – This is a number guessed by the user that is close to the expected internal rate of return (as there can be two solutions for the internal rate of return). If omitted, the function will take a default value of 0.1 (=10%).

Example:

For an initial investment of 1,000,000, the cash flows are given below:

 Initial Investment -1000000 (It should be –(minus) 1000000 as the money is gone out from the pocket) Year 1 Income 1,50,000 Year 2 Income 1,50,000 Year 3 Income 1,50,000 Year 4 Income 1,50,000 Year 5 Income 1,50,000 Year 6 Income 2,50,000 Year 7 Income 2,50,000 Year 8 Income 2,50,000 Year 9 Income 3,50,000 Year 10 Income 3,50,000 =IRR(Initial Investment: Year 10 Income) =14%

Example 2

Let us calculate the CAGR

 Year Revenue 2011 2,000,000 2012 2,500,000 2013 2,750,000 2014 3,000,000 2015 3,500,000 2016 3,750,000 2017 4,000,000

The IRR function is not exactly designed for calculating compound growth rate, so we need to reshape the original data in this way:

The beginning value of the investment should be entered as a negative number. The ending value of the investment is a positive number. All intermediate values are kept as zeros.

Now, we would simply apply the IRR function to calculate the CAGR, as shown below:

 Year Revenue 2011  (E2—EXCEL ) (2,000,000) 2012 0 2013 0 2014 0 2015 0 2016 0 2017  (E8) 4,000,000 =IRR =IRR(E2:E8) =12%  =      CAGR=12%

25)

ISPMT:– This function calculates the interest paid during a specific period of investment. For eg: If I wanted to know the Interest for 6th Month or 8th Month. By Putting ISPMT function, we can calculate.

This function is useful for financial analyst as it helps predict interest payments while preparing revenue forecasts, annual budgets, etc. For example, suppose a business takes out a loan and we need to know how much interest would be paid in a particular period. Using the ISPMT function, we can find out the interest due in the future.

Syntax :

=ISPMT(rate, per, nper, pv)

·        Rate (required argument) – This is the interest rate on the investment or loan.

·        Per (required argument) – This is the period for which we want to calculate the interest. It must be an integer between 1 and nper.

·        Nper (required argument) – The number of periods over which the loan or investment must be paid back.

·        Pv (required argument) – This is the present value of the loan/investment. It can be the total value of payments made to date.

Eg:

Note:

Outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers

 PV (Present value) or (Fixed Amount) Rate (Interest Rate) per annum Per (Terms) (Period which we calculate Interest) Nper (number of periods) (total period in which money to paid back) ISPMT 2500000 8% (Annum) 1 (Month) 12 Months =ISPMT(rate,per,nper,pv) =ISPMT(8%/12,1,12,250000)= -1527.777778 5000000 6% 14 Month 24 Month =ISPMT(rate,per,nper,pv) =ISPMT(6%/12,14,24,500000)= -1041.666667 1000000 5% 32 Month 36 Month =ISPMT(rate,per,nper,pv) =ISPMT(5%/12,32,36,1000000)= -462.962963

26)

MDURATION:- This function calculates the Modified Duration of Bond Amount. This is used on Share Market (Shares or Bond or Equity), Commodity etc.

Syntax:

=MDURATION(settlement,maturity,coupon,yld,frequency,[basis])

·        Settlement (required argument) – This is the settlement date of the security after the issue date when the security is traded to the buyer.

·        Maturity (required argument) – This is the maturity date of the security or the date when the security is matured.

·        Coupon (required argument) – The annual coupon rate.

·        Yld (required argument) – This is the annual yield of the security.

·        Frequency (required argument) – This is the number of coupon payments per year. The frequency is 1 for annual payments, 2 for semi-annual, and 4 for quarterly payments.

·        Basis (optional argument) – This is the type of day count basis to use. Use 0  (30/360—for US/NSD) ; 1 (Actual/Actual) ; 2 (Actual/360) ; 3 (Actual/365 for India) ; 4 (30/360 for European countries)

Eg:

 Settlement Date Maturity Date Coupon (Annual rate) Yield(Yld) (Annual Yield) You will get once it is matured Frequency Basis MDURATION Jan-01-2018 Dec-31-2022 2% 3% 1 3 =MDURATION(settlement,maturity,coupon,yld,frequency,[basis]) =MDURATION(Jan-01-2018,Dec-31-2022,2%,3%,1,3) =4.659812894=4.66 (4 years 66 days) Jan-01-2018 Dec-31-2022 2% 3% 2 3 =MDURATION(settlement,maturity,coupon,yld,frequency,[basis]) =MDURATION(Jan-01-2018,Dec-31-2022,2%,3%,2,3) =4.703246502=4.70 =(4 years 70 days) Jan-01-2018 Dec-31-2022 2% 3% 4 3 =MDURATION(settlement,maturity,coupon,yld,frequency,[basis]) =MDURATION(Jan-01-2018,Dec-31-2022,2%,3%,4,3) =4.725350316 =4.73 (4 years 73 days)

27)

MIRR:  Also called Modified Internal Rate of Return (for a series of periodic cash flows). MIRR considers both the cost of the investment and the interest received on reinvestment of cash.

The Internal Rate of Return (IRR) assumes that all cash flows received from an investment are reinvested at the same rate.

The Modified Internal Rate of Return (MIRR) allows you to set a different reinvestment rate for cash flows received.

Syntax :–

=MIRR(cash flows,financing rate,reinvestment rate)

·        Cash Flows (Values) – Required. Individual cash flows representing a series of payments (negative values) and income (positive values) occurring at regular periods. It must contain at least one positive value and one negative value to calculate the modified internal rate of return.

·        Financing Rate – Required. The interest rate you pay on the money used in the cash flows.

·        Reinvestment Rate –  Required. The interest rate you receive on the cash flows as you reinvest them.

Eg:

Note: A1:A7 (Excel Shift)

 Year Cash Flows Finance Rate 11% 0 Year (A1) -1000 (Initial Investment) Re-Invest Rate 9% 1 Year (A2) 100 (Return on 01st yr) 2 Year (A3) 200 (Return on 2nd yr) 3 Year (A4) 350 (Return on 3rd yr) 4 Year (A5) 400 5 Year (A6) 450 6 Year (A7) 550 MIRR MIRR MIRR =MIRR(cash flows,financing rate,reinvestment rate) =MIRR(A1:A7,11%,9%) 16%

28)

NOMINAL:–This function returns the nominal annual interest rate when the effective rate and number of compounding years are given.

In financial analysis, we often evaluate more than one bond and hence are interested in knowing the net return offered by each. If two bonds are to be compared, one advertising a real interest rate while the other a nominal rate, the NOMINAL function can be helpful.

Syntax:

=NOMINAL(effect_rate,npery)

·        Effect_rate (required argument) – This is the effective interest rate.

·        Npery (required argument) – This is the number of compounding periods per year.

Eg:

 Effect_Rate Npery NOMINAL 7% 5 =NOMINAL(effect_rate,npery) ;  =NOMINAL(7%,5) =0.06811849 = 0.06811 = 0.06811*100 = 6.81% (Nominal Rate of Return) 5% 4 =NOMINAL(effect_rate,npery) ;  =NOMINAL(5%,4) =0.049088938 = 0.0491*100 = 4.91% (Nominal Rate of Return) 9% 7 =NOMINAL(effect_rate,npery) ;  =NOMINAL(9%,7) =0.086710351 = 0.08671*100= 8.67% (Nominal Rate of Return)

29)

NPER :– Total number of Period. This function helps to calculate the number of periods that are required to pay off a loan or reach an investment goal through regular periodic payments and at a fixed interest rate.

Syntax:

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

Rate (required argument) – This is the interest rate per period

Pmt (required argument)—(Monthly Payment) The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes.

Pv (required argument) – The present value, or the lump-sum amount that a series of future payments is worth right now.

Fv (optional argument) – This is the future value or the cash balance which we want at the end after the last payment is made. When omitted, it takes the value as zero.

Type (optional argument) – Indicates when payments are due. If type is set to 0 or omitted, then payments are due at the end of the period. If set to 1, payments are due at the start of the period.

Eg:

Note

·        We input the payment for the loan as a negative value, as it represents an outgoing payment.

·        Payments are made monthly, so we needed to convert the annual interest rate of 5% into a monthly rate (=5%/12).

 Pv  (Present Value) (Loan Amount) Rate (Interest rate per annum) PMT (Monthly Payment) NPER (Net Period required to Pay complete Loan Amount) 1500000 5% per annum 12000 =nper(rate,pmt,pv,[fv].[type]) =nper(5%/12,12000,1500000,0,0)= -100.8315072 (It means total period is 100 months to pay loan 1500000 5% per annum 15000 =nper(rate,pmt,pv,[fv].[type]) =nper(5%/12,15000,1500000,0,0)= -83.76763928 (It means total period is 83 months to pay loan 1500000 5% per annum 16000 =nper(rate,pmt,pv,[fv].[type]) =nper(5%/12,16000,1500000,0,0)= -79.30555111 (It means total period is 79 months to pay loan

30)

NPV :–Net Present Value. It will calculate the Net Present Value (NPV) for a series of cash flows and a given discount rate. In financial modeling, the NPV function is useful in determining the value of an investment or understanding the feasibility of a project.

Syntax:

=NPV(rate,value1,[value2],…………)

·        Rate (required argument) – This is the rate of discount over the length of the period.

·        Value1, Value2 – Value1 is a required option. They are numeric values that represent a series of payments and income where:

·        Negative payments represent outgoing payments.

·        Positive payments represent incoming payments.

Eg:

Note:

The NPV formula is based on future cash flows. If the first cash flow occurs at the start of the first period, the first value must be added to the NPV result, not included in the values arguments.

 Year Data Description 0 -100000 (Cash is flowing out) B1 Investment Amount 1 25000   B2 1st Year Return 2 25000   B3 2nd Year Return 3 25000    B4 3rd Year Return 4 25000    B5 4th  Year Return 5 25000    B6 5th Year Return Investment Amount 100000 Discount rate 4% NPV =NPV(rate,value1,[value2]…..) =NPV(4%/12,b2:b6)+b1=  23759.66 is the Net Present Value