Excel Calculation & Its Meaning IV
Introduction
In our previous series of Financial Calculation through MSExcel 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
MSExcel Financial Terms 
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) ; 430/360 (European)
Lets calculate it through using INTRATE function as well as Formula
Settlement date 
Jan 012017 
Maturity date 
Dec312017 
Investment done 
100000 
Redemption 
102000 
=INTRATE 
=INTRATE(Jan012017,De312017,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 semiannual, 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 
Jan012018 
Dec312022 
2% 
3% 
1 
3 
=MDURATION(settlement,maturity,coupon,yld,frequency,[basis]) =MDURATION(Jan012018,Dec312022,2%,3%,1,3) =4.659812894=4.66 (4 years 66 days) 
Jan012018 
Dec312022 
2% 
3% 
2 
3 
=MDURATION(settlement,maturity,coupon,yld,frequency,[basis]) =MDURATION(Jan012018,Dec312022,2%,3%,2,3) =4.703246502=4.70 =(4 years 70 days) 
Jan012018 
Dec312022 
2% 
3% 
4 
3 
=MDURATION(settlement,maturity,coupon,yld,frequency,[basis]) =MDURATION(Jan012018,Dec312022,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) 
ReInvest Rate 
9% 
1 Year (A2) 
100 (Return on 01^{st} yr) 


2 Year (A3) 
200 (Return on 2^{nd} yr) 


3 Year (A4) 
350 (Return on 3^{rd} 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 lumpsum 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 
1^{st} Year Return 

2 
25000 B3 
2^{nd} Year Return 

3 
25000 B4 
3^{rd} Year Return 

4 
25000 B5 
4^{th} Year Return 

5 
25000 B6 
5^{th} 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 
