# Excel Calculation & Its Meaning-V

Introduction

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

·        INTRATE

·        IRR

·        ISPMT

·        MDURATION

·        MIRR

·        NOMINAL

·        NPER

·        NPV

Now , In this Post We will cover some financial terms like

·        ODDFPRICE

·        ODDFYIELD

·        ODDLPRICE

·        ODDLYIELD

·        PDURATION

For More details regarding Microsoft Excel and its function , Please click the Link Below

https://www.thearticle111.com/2021/01/excel-calculation-its-meaning-viii.html

 MS-Excel Terms

31)

ODDFPRICE-- Odd First Price. It calculates (or returns) the price per \$100 face value of a security or bond with an odd first period. Some Bonds come with Irregular first or last period and due to that the payment doesn't fit in any of the usual patterns.

So, to calculate the price of bond with an Odd first period, we have to use ODDFPRICE Excel function.

Syntax :

=ODDFPRICE(Settlement, Maturity, Issue, First_Coupon, Rate, Yld,

Redemption, Frequency, [Basis])

Note:

For Stocks--Face value is the original cost of the Stocks, as listed on

the certificate.

For Bonds-- It is the amount paid to the holder at maturity, typically

in \$1,000 denominations. The  face value for bonds is

often referred to as "par value" or simply "par."

• Settlement (required argument) – This is the security’s settlement date. It is the date after the issue date when the security is traded to the buyer.
• Maturity (required argument) – This is the security’s maturity date. It is the date when the security is matured.
• Issue (required argument) – This is the security’s issue date.
• First_coupon (required argument) – This is the security’s first coupon date.
• Rate (required argument) – The security’s interest rate.
• Yld (required argument) – The security’s annual yield.
• Redemption (required argument) – This is the security’s redemption value per \$100 face value.
• Frequency (required argument) – This is the number of coupon payments per year. For annual payments, frequency = 1; for semi-annual, frequency = 2; for quarterly, frequency = 4.
• Basis (optional argument) – This is the type of day count basis to use. 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 Maturity date Issue date First coupon (when you get the return) Interest Rate Yld (Annual Yield) Redemption Frequency & Basis ODDFPRICE =ODDFRICE(settlement,maturity,issue,firstcoupon,rate,yld,redemption,frequency,[basis]) March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) Dec 31, 2017 (12/31/2017) 4% 5% 325 1 & 3 =(03/01/2017,12/31/2018,02/02/2017,12/31/2017,4%,5%,325,1,3) =304.0127951 =304.01 March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) Dec 31, 2017 (12/31/2017) 4% 5% 325 2 & 3 =(03/01/2017,12/31/2018,02/02/2017,12/31/2017,4%,5%,325,2,3) =296.3579096 =296.36 March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) Dec 31, 2017 (12/31/2017) 4% 5% 325 4 & 3 =(03/01/2017,12/31/2018,02/02/2017,12/31/2017,4%,5%,325,4,3) =303.6107534 =303.61

32)

ODDFYIELDOdd First Yield. It helps calculate (returns) the yield of a security with an odd (short or long) first period. Some bonds come with irregular first or last periods and the payment doesn’t fit in any of the given patterns. To calculate the yield of a security, we can use the ODDFYIELD function for such bonds.

Syntax  --

=ODDFYIELD((settlement, maturity, issue, first_coupon, rate, pr,

redemption, frequency, [basis])

• Settlement (required argument) This is the security’s settlement date. It is the date after the issue date when the security is traded to the buyer.
• Maturity (required argument) – This is the security’s maturity date. It is the date when the security matured.
• Issue (required argument) – This is the security’s issue date.
• First_coupon (required argument)This is the security’s first coupon date.
• Rate (required argument)The security’s interest rate.
• pr (required argument) – The security’s (Shares or Bond) price.
• Redemption (required argument)This is the security’s redemption value per \$100 face value.
• Frequency (required argument)This is the number of coupon payments per year. For annual payments, frequency = 1; for semi-annual, frequency = 2; for quarterly, frequency = 4.
• Basis (optional argument) – This is the type of day count basis to use. 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 Maturity date Issue date First coupon (when you get the return) Interest Rate Price Value (pr) Redemption Frequency & Basis ODDFYIELD =ODDFYIELD(settlement,maturity,issue,firstcoupon,rate,pr,redemption,frequency,[basis]) March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) Dec 31, 2017 (12/31/2017) 4% 100 325 1 & 3 =(03/01/2017,12/31/2018,02/02/2017,12/31/2017,4%,100,325,1,3) =0.932232016 =0.932232016*100 =93.22% =93% March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) Dec 31, 2017 (12/31/2017) 4% 100 325 2 & 3 =(03/01/2017,12/31/2018,02/02/2017,12/31/2017,4%,100,325,2,3) =0.591428633 =0.591428633*100 =59.14% =59% March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) Dec 31, 2017 (12/31/2017) 4% 100 325 4 & 3 =(03/01/2017,12/31/2018,02/02/2017,12/31/2017,4%,100,325,4,3) =0.719737673  =0.719737673*100 =71.97% =72%

33

ODDLPRICE—Odd Last Price. It calculates the price per \$100 face value of a security with an odd last period. Some bonds come with irregular first or last periods and the payment doesn’t fit in any of the given patterns.

To calculate the price of a bond with an odd last period, we can use the ODDLPRICE function.

Syntax:

=ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption,

frequency, [basis])

·        Settlement (required argument)This is the security’s settlement date. it is the date after the issue date when the security is traded to the buyer.

·        Maturity (required argument) – This is the security’s maturity date. It is the date when the security expires.

• Last_interest (required argument)This is the security’s last coupon date.
• Rate (required argument)The security’s interest rate.
• Yld (required argument)The security’s annual yield.
• Redemption (required argument)This is the security’s redemption value per \$100 face value.
• Frequency (required argument) – This is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
• Basis (optional argument) – It is the type of day count basis to use. 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 Maturity date Last Interest date Rate Yld (Annual Yield) Redemption value ODDLPRICE =ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis]) March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) 4% 5% 325 =(03/01/2017,12/31/2018,02/02/2017, 4%,5%,325,1,3) =304.379141 =304.38 March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) 4% 5% 325 =(03/01/2017,12/31/2018,02/02/2017, 4%,5%,325,2,3) =304.3776588 =304.38 March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) 4% 5% 325 =(03/01/2017,12/31/2018,02/02/2017, 4%,5%,325,4,3) =304.3901714 =304.39

34)

ODDLYIELD: --Odd Last Yield. It helps calculate the yield of a security with an odd (short or long) last period. Some bonds come with irregular first or last periods and the payment sometimes doesn’t fit in any of the given patterns.

To calculate the yield of a security with an odd last period, we can use the ODDLYIELD function for such bonds.

Syntax:

=ODDLYIELD(settlement, maturity, issue, last_interest, rate, pr,

redemption, frequency, [basis])

·        Settlement (required argument)This is the security’s settlement date. It is the date after the issue date when the security is traded to the buyer.

·        Maturity (required argument) – This is the security’s maturity date. It is the date when the security expires.

• Last_interest (required argument)This is the security’s last coupon date.
• Rate (required argument)The security’s interest rate.
• Pr (required argument)The security’s (Shares or Bond) price.
• Redemption (required argument)This is the security’s redemption value per \$100 face value.
• Frequency (required argument)This is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
• Basis (optional argument)It is the type of day count basis to use. 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 Maturity date Last Interest date Interest Rate Price Value (pr) Redemption value ODDLYIELD =ODDLYIELD(settlement, maturity, issue, last_interest, rate, pr, redemption, frequency, [basis]) March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) 4% 100 325 =(03/01/2017,12/31/2018,02/02/2017,4%,100,325,1,3) =1.262012096 =1.262012096*100 =126.20% =126% March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) 4% 100 325 =(03/01/2017,12/31/2018,02/02/2017,4%,100,325,2,3) =1.261898683 =1.261898683*100 =126.19% =126% March 01,2017(03/01/2017) Dec 31,2018 (12/31/2018) Feb 2, 2017 (02/02/2017) 4% 100 325 =(03/01/2017,12/31/2018,02/02/2017,4%,100,325,4,3) =1.262672757 =1.262672757*100 =126.27% =126%

35)

PDURATION :-- The PDURATION function calculates how much time is needed for an initial investment (present value) to reach a given amount (future value), assuming a constant annual interest rate. PDURATION returns an amount of time in periods, which is linked to the number of compounding periods per year.

Syntax :--

=PDURATION (rate, pv,fv)

• Rate (required argument)This is the interest period per year.
• Pv (required argument)The present value (Initial value) of the investment.
• Fv (required argument)This is the future value of the investment.

Eg:

 Interest Rate PV (Present Value) FV (Future Value) PDURATION  value 4% 5000 8000 11.98355643 (11 yrs 98 days) 4% 6000 7000 3.930338524 (3 yrs 93 days) 4% 7000 8500 4.950343806 (4 yrs 95 days)

### Milan Tomic

Hi. I’m Designer of Blog Magic. I’m CEO/Founder of ThemeXpose. I’m Creative Art Director, Web Designer, UI/UX Designer, Interaction Designer, Industrial Designer, Web Developer, Business Enthusiast, StartUp Enthusiast, Speaker, Writer and Photographer. Inspired to make things looks better.

Blogger Comment