Excel Calculation & Its Meaning V

by

                     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, Excel Calculation & Its Meaning V  We will cover some financial terms like

·        ODDFPRICE

·        ODDFYIELD

·        ODDLPRICE

·        ODDLYIELD

·        PDURATION

                        

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)

ODDFYIELD—Odd 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)

 

 

 

 

Post Views: 186

Leave a Comment