Excel Calculation & Its Meaning IV

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 6^th Month or 8^th 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