Excel Calculation & Its Meaning VIII
Introduction
In our previous series of Financial Calculation through MSExcel tools , we covered
· SLN
· SYD
· TBILLEQ
· TBILLPRICE
· TBILLYIELD
· VDB
In this Post Excel Calculation & Its Meaning VIII We will cover some financial terms like
· XIRR
· XNPV
· YIELD
· YIELDDISC
· YIELDMAT
MSExcel Financial Terms 
49)
XIRR:– This function calculate the Internal Rate of Return (IRR) for a series of cash flows that may not be periodic. It does this by assigning specific dates to each individual cash flow.
This function is useful in determining the value of an investment or understanding the feasibility of a project that does not have regularly periodic cash flows. It helps us understand the rate of return earned on an investment.
Syntax :
=XIRR(values, dates,[guess])
· Values (required argument) – This is the array of values that represent the series of cash flows. These must include at least one negative value (representing outgoing payment) and at least one positive value (representing income).
· Dates (required argument) – This is a series of dates that correspond to the given (or supplied) values. The first date is the start of the loan/investment period and the subsequent dates refer to the dates of further outgoing payments or income. Therefore, subsequent dates must be later than the first date.
· [guess] (optional argument) – This is an initial guess – or estimate – of what the IRR will be. If omitted, Excel takes the default value of 10%. (Note: the [guess] is only a value for Excel to start off working with – Excel then uses an iterative procedure to converge to the XIRR).
Eg:
Suppose I invested an amount of Rs1000 (1000) [it is written in negative as cash is flowing out] on Jan 01 2018. I get return 400,300,200,200 on dates (July 1, Aug 1, Sept 1, Oct 1) 2018. So, We need to Calculate the Internal Rate of Return for a series of Cash flows
Value of Investment 
Values of Cash flow (values) 
Dates of Cash flow 
1000 
1000 (b5) 
01012018 (Jan 01, 2018) [c5] 
400(b6) 
07012018 (July 01, 2018) [c6] 

300 (b7) 
08012018 (Aug 01, 2018) [c7] 

200 (b8) 
09012018 (Sept 01, 2018) [c8] 

200 (b9) 
10012018 (Oct 01, 2018) [c9] 

XIRR formula 
=XIRR(values,dates,[guess]) =XIRR(b5:b9,c5:c9,0) =0.173708147 =0.173708147*100 (Since we have to calculate in interest rate) =17.37% 
50)
XNPV:– The Excel XNPV function is a financial function that calculates the net present value (NPV) of an investment using a discount rate and a series of cash flows that occur at irregular intervals.
Syntax :
=XNPV(Rate, Cash Flows, Dates of Cash Flow)
· Rate (Required Argument) The discount rate to apply to the cash flows.
· Values (required argument) – This is the array of values that represent the series of cash flows. These must include at least one negative value (representing outgoing payment) and at least one positive value (representing income).All succeeding payments are discounted based on a 365day year
· Dates (required argument) – This is a series of dates that correspond to the given (or supplied) values. The first date is the start of the loan/investment period and the subsequent dates refer to the dates of further outgoing payments or income. Therefore, subsequent dates must be later than the first date.
Eg:
If I invested amount of Rs.10000 (10000 will be written as the money is flowing out) on Jan 01,2015 and the returns like 2750,4250,3250,2750 is coming on a date [March 01, Oct 30] 2015 & [Feb 15, April 01] 2016 then the Net present value of an investment will be
Discount Rate 
Series of Cash flow (values) 
Dates (Series of dates to the given values) 
8% [a5] 
10000 [b5] 
01/01/2015 (Jan012015) [c5] 
2750 [b6] 
03/01/2015 (March 01, 2015) [c6] 

4250 [b7] 
10/30/2015 (Oct 30, 2015) [c7] 

3250 [b8] 
02/15/2016 (Feb 15, 2016) [c8] 

2750 [b9] 
04/01/2016 (April 01, 2016) [c9] 

XNPV formula 
=XNPV( a5,b5:b9,c5:c9) =2182.555091 =2182.56 
51)
YIELD :– This function calculate the yield on a security that pays periodic interest. It is generally used to calculate bond yield. YIELD is different from the rate of return, as the return is the gain already earned, while yield is the prospective return.
Syntax:–
= YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
· Settlement (required argument) – This is the settlement date of the security. It is a date after the security is traded to the buyer that is after the issue date.
· Maturity (required argument) – This is the maturity date of the security. It is the date when the security is matured.
· Rate (required argument) – The annual coupon rate of a Security (Bond)
· PRs (required argument) – The price of the security per $100 face value.
· Redemption (required argument) – This is the redemption value of the bond per $100 of face value.
· Frequency (required argument) – The number of coupon payments per year. It must be one of the following: 1 – Annually, 2 – Semiannually, 4 – Quarterly
· [basis] (optional argument) – It specifies the financial day count basis that is used by the security. Use — 0 (for US/NASD 30/360) ; 1(for Actual/Actual) ; 2(for Actual/360) ; 3(for India Actual/365) ; 4(for European Countries 30/360)
Eg:
Settlement date 
01/01/2010 
YIELD = YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

Maturity date 
06/30/2015 
When Frequency is Annual and Basis: 3 =YIELD(01/01/2010,06/30/2015,10%,101,100,1,3) =0.097282247 =0.097282247*100 =9.728% 
Rate 
10% 
When Frequency is SemiAnnual and Basis: 3 =YIELD(01/01/2010,06/30/2015,10%,101,100,2,3) =0.097604893 =0.097604893*100 =9.76% 
Pr 
101 
When Frequency is Quarterly and Basis:3 When Frequency is Annual and Basis: 3 =YIELD(01/01/2010,06/30/2015,10%,101,100,4,3) =0.097626958 =0.097626958*100 =9.76% 
Redemption 
100 

52)
YIELDDISC:– This function returns the annual yield for a discounted security, such as a Treasury bill, that is issued at a discount
but that matures at face value. The YIELDDISC function is not the same as the YIELD function, as it calculates the yield on a discounted security.
Syntax
=YIELDDISC(settlement, maturity, pr, redemption, [basis])
· Settlement (required argument) – This is the settlement date of the security. It is a date after the security is traded to the buyer – that is, after the issue date.
· Maturity (required argument) – This is the maturity date of the security. It is the date when the security is matured.
· Pr (required argument) – The price of the security per $100 face value.
· Redemption (required argument) – This is the redemption value of the bond per $100 of face value.
· [basis] (optional argument) –– It specifies the financial day count basis that is used by the security. Use — 0 (for US/NASD 30/360) ; 1(for Actual/Actual) ; 2(for Actual/360) ; 3(for India Actual/365) ; 4(for European Countries 30/360)
Eg:
Settlement date 
01/01/2017 (Jan 01, 2017) 
Maturity date 
06/30/2017 (June 30, 2017) 
Price per 100 face value (Pr) 
97 
Redemption 
100 
basis 
3 [for India] 
=YIELDDISC(settlement, maturity, pr, redemption, [basis]) 
=YIELDDISC(01/01/2017,06/30/2017,97,100,3) =0.062714777 =0.062714777*100 =6.27% 
53)
YIELDMAT :– This function returns (or calculates) the annual yield of a security (bond) that pays interest at maturity. The annual yield is the total increase in value in one year.
Syntax
=YIELDMAT(settlement, maturity, issue, rate, pr, [basis])
Settlement (required argument) – This is the settlement date of the security. The Settlement date should be after the issue date when the security is traded to the buyer.
The settlement date is the date a buyer purchases a security such as a bond. The maturity date is the date when the coupon is matured. For example, suppose a 30year bond is issued on January 1, 2009, and is purchased by a buyer six months later. The issue date would be January 1, 2009, the settlement date would be July 1, 2009, and the maturity date would be January 1, 2039, which is 30 years after the issue date of January 1, 2009.
Maturity (required argument) – This is the maturity date of the security. It is the date when the security is matured.
Issue (required argument) – This is the issue date of the security. It is expressed as a serial date number.
Rate (required argument) – The interest rate of the security at the date of issue.
Pr (required argument) – The price of the security per $100 face value.
Basis (optional argument) – It specifies the financial day count basis that is used by the security. Use — 0 (for US/NASD 30/360) ; 1(for Actual/Actual) ; 2(for Actual/360) ; 3(for India Actual/365) ; 4(for European Countries 30/360)
Eg:
Settlement date 
01/01/2017 (Jan 01, 2017) 
Maturity date 
06/30/2018 (June 30, 2018) 
Issue date 
07/01/2015 (July 01, 2015) 
Interest Rate 
6.50% 
Price of security per 100 face value (Pr) 
101 
=YIELDMAT(settlement, maturity, issue, rate, pr, [basis])

=YIELDMAT(01/01/2017,06/30/2018,07/01/2015,6.50%,101,3) [basis=3 for India] =0.052622415 =0.052622415*100 =5.26% 