Excel Calculation & Its Meaning VIII

 

Introduction

MS-Excel 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)	01-01-2018 (Jan 01, 2018) [c5]
	400(b6)	07-01-2018 (July 01, 2018) [c6]
	300 (b7)	08-01-2018 (Aug 01, 2018) [c7]
	200 (b8)	09-01-2018 (Sept 01, 2018) [c8]
	200 (b9)	10-01-2018 (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 365-day 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 (Jan-01-2015)                        [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 – Semi-annually, 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 Semi-Annual 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 30-year 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%