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) |