Excel Calculation & Its Meaning VI

Introduction

MS-Excel Terms

 

 36)

PRICE:–  It will calculate the price of a bond per $100 face value that pays a periodic interest rate.

This PRICE function can be useful when we wish to borrow money by selling bonds instead of stocks. If we know the parameters of the bond to be issued, we can calculate the breakeven price of a bond using this function.

 Syntax                     

               =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

Settlement (required argument) – The bond’s settlement date or the date that the coupon is purchased. The bond’s settlement date should be after the issue date.

Maturity (required argument) – This is the bond’s maturity date or the date when the bond is matured.

To understand settlement and maturity, let’s take an example: a 30-year bond that was issued on January 1, 2017, and is purchased by a buyer six months later. The issue date would be January 1, 2017, the settlement date would be July 1, 2017, and the maturity date would be January 1, 2047, which is 30 years after the January 1, 2017 issue date.

Rate (required argument) – This is the annual coupon rate of the bond.

Yld (required argument) – The annual yield of the bond.

Redemption (required argument) – This is the redemption value of the bond per $100 face value.

Frequency (required argument) – The number of coupon payments per year.

Basis (optional argument) – Specifies the financial day count basis that is used by the bond.

 Eg:

 

Settlement date	Maturity date	Annual Interest Rate	Yld (Annual Yield)	Redemption value	PRICE =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
April01,2017(04/01/2017)	March 31,2025 (03/31/2025)	9.5%	8%	500	=PRICE(04/01/2017,03/31/2025,9.5%,8%,1,3) =324.7699615 =324.77
April01,2017(04/01/2017)	March 31,2025 (03/31/2025)	9.5%	8%	500	=PRICE(04/01/2017,03/31/2025,9.5%,8%,2,3) =322.3457374 =322.35
April01,2017(04/01/2017)	March 31,2025 (03/31/2025)	9.5%	8%	500	=(04/01/2017,03/31/2025,9.5%,8%,4,3) =321.0976007 =321.10

 

37)

PRICEDISC:– It will calculate the price of a bond per $100 face value of a discounted security. It is useful when we wish to borrow money by selling bonds instead of stocks.

If we know the redemption value, interest rate, and the start and end dates, we can calculate the bond’s price using the function.

Syntax :   

                     =PRICEDISC(settlement, maturity, discount, redemption, [basis])

·        Settlement (required argument) – This is the security’s settlement date or the date the coupon is purchased. The security’s settlement date should be after the issue date.

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

·        Discount (required argument) – The discount rate of the security (The Security percentage discount rate)

·        Redemption (required argument) – This is the redemption value of the security per $100 face value.

·        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	Annual Discount Rate	Redemption value	PRICEDISC =PRICEDISC(settlement, maturity, discount, redemption, [basis])
March-01-2018 (03/01/2018)	Dec-31-2018 (12/31/2018)	5%	100	=PRICEDISC(03/01/2018,12/31/2018,5%,100,3) =95.82191781 =95.82
March-01-2018 (03/01/2018)	Dec-31-2018 (12/31/2018)	6%	100	=PRICEDISC(03/01/2018,12/31/2018,6%,100,3) =94.98630137 =94.99
March-01-2018 (03/01/2018)	Dec-31-2018 (12/31/2018)	7%	100	=PRICEDISC(03/01/2018,12/31/2018,7%,100,3) =94.15068493 =94.15

 

38) 

PRICEMAT:– This function will calculate the price of a bond that pays interest at maturity, per $100 face value. It can be useful when we wish to borrow money by selling bonds  instead of stocks. We need to know the minimum figure to charge investors. We can calculate its price per $100 of redemption value using PRICEMAT.

Syntax :–   

                          =PRICEMAT(settlement, maturity, issue, rate, yld, [basis])

·        Settlement (required argument) – The security’s settlement date or the date that the coupon is purchased. The security’s settlement date should be after the issue date.

·        Maturity (required argument) – This is the security’s (Shares or Bond) maturity date or the date when the security is matured.

·        Issue (required argument) – The security’s issue date that is expressed as a serial date number.

·        Rate (required argument) – This is the interest rate on the security on the date of issue.

·        Yld (required argument) – This is the annual yield of the security.

·        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	Maturity date	Issue date	Annual Interest rate	Annual Yield	PRICEMAT =PRICEMAT(settlement, maturity, issue, rate, yld, [basis])
March-01-2018 (03/01/2018)	Dec-31-2018 (12/31/2018)	Feb-02-2018 (02/02/2018)	5%	6%	=PRICEMAT(03/01/2018,12/31/2018,02/02/2018,5%,6%,3) =99.18662016 =99.19
March-01-2018 (03/01/2018)	Dec-31-2018 (12/31/2018)	Feb-02-2018 (02/02/2018)	6%	6%	=PRICEMAT(03/01/2018,12/31/2018,02/02/2018,6%,6%,3) =99.97880983 =99.98
March-01-2018 (03/01/2018)	Dec-31-2018 (12/31/2018)	Feb-02-2018 (02/02/2018)	7%	6%	=PRICEMAT(03/01/2018,12/31/2018,02/02/2018,7%,6%,3) =100.7709995 =100.77

39)

PV:– This function calculates the present value of a loan or an investment. In financial statement analysis, PV is used to calculate the dollar value of future payments in the present time

Syntax:                      

                      =PV(rate, nper, pmt, [fv], [type])

·        Rate (required argument) – The interest rate per compounding period. A loan with a 12% annual interest rate and monthly required payments would have a monthly interest rate of 12%/12 or 1%. Therefore, the rate would be 1%.

·        nper (required argument) – The number of payment periods. For example, a 3 year loan with monthly payments would have 36 periods. Therefore, nper would be 36 months.

·        pmt (required argument) – The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. For example, the monthly payments on a 10,000, four-year car loan at 12 percent are 263.33. You would enter -263.33 into the formula as the pmt. If pmt is omitted, you must include the fv argument.

·        fv (optional argument) – An investment’s future value at the end of all payment periods (nper). If there is no input for fv, Excel will assume the input is 0.

·        type (optional argument) – Type indicates when payments are issued. There are only two inputs, 0 and 1. If type is omitted or 0 is the input, payments are made at period end. If set to 1, payments are made at period beginning.

Eg:

Note: IF the money is out from the pocket, we write as negative

 

Rate of Interest (must be calculated monthly)	Nper (total number of payment period in months)	PMT (Monthly EMI payment)	PV =PV(rate, nper, -pmt, [fv], [type])
15%= 15%/12	60 Months	-11894.97	=PV(15%/12,60,-(-11894.97),0,0) =500,000.21
12%= 12%/12	36 Months	-1992.86	=PV(12%/12,36,-(-1992.86),0,0) =60000.04

 

 

40) 

RATE :- This function is used to  calculate the interest rate charged on a loan or the rate of return needed to reach a specified amount on an investment over a given period.

Syntax:–                           

                       =RATE(nper, pmt, pv, [fv], [type], [guess])

·        Nper (required argument) – The total number of periods over which the loan or investment is to be paid.

·        Pmt (required argument) – This is the payment for each period. This number must be unchanged over the life of the loan. Pmt includes principal and interest but no other fees or taxes. If pmt is omitted, fv must be inputted.

·        PV (required argument) – The present value [Total Loan Amount] of all future payments; what all future payments would be worth in the present.

·        FV (optional argument) – This is the future value that is the goal of the investment. This value is what we aim to have after the last payment is made. If we omit fv, it is assumed to be 0 (the future value of a loan, for example, is 0) and we must include a pmt argument instead.

·        Type (optional argument) – Determines how the formula will consider the due dates for payments. If type is omitted or 0 is inputted, payments are due at the end of  period . If 1 is inputted, payments are due at beginning of the period.

·        Guess (optional argument) – Our guess of what the interest rate should be. This provides a start point for the RATE function so that it may converge on an answer easier before reaching 20 iterations.

·        When omitted, RATE assumes the guess to be 10%.

·        If RATE does not converge, attempt other values for this input.

 Eg:

 

PV (Loan Amount)	Total yrs Nper (total EMI period in months)	Interest Rate (Monthly)	PMT (Monthly Installment)	RATE (Annually) =RATE(nper, pmt, pv, [fv], [type], [guess])
200000	3yrs (3*12=36months)	6% (6%/12)	-6084.39 (cash flows out so minus sign)	=Rate(36,PMT,200000,0,0)*12 =0.060000277 =0.060000277*100 (convert into %) =6% Annually
600000	5yrs(5*12=60months)	7% (7%/12)	-11880.72	=Rate(60,PMT,600000,0,0)*12 =0.070000031 =0.070000031*100 (convert into %) =7% Annually

 

41)

RECEIVED:– The RECEIVED function calculates the amount received at maturity for a fully invested security. A fully invested security (Insurance, MF etc.) does not pay periodic interest before maturity.

This formula is used when you invested money in Insurance, Mutual Fund .

Maturity RECEIVED Formula        

                            =RECEIVED(settlement, maturity, investment, discount, [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) – The security’s maturity date or when it expires.

·        Investment (required argument) – This is the amount invested in the security.

·        Discount (required argument) – This is the security’s discount rate.

·        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 (Start date)	Maturity date (End date)	Investment (Fixed Amount)	Interest rate (Annually)	RECEIVED =RECEIVED(settlement, maturity, investment, discount, [basis])
May-01-2018(05/01/2018	April-30-2023 (04/30/2023)	100000	6%	=RECEIVED(05/01/2018,04/30/2023,100000,6%,3) =142857.1429 =142857.14
May-01-2018(05/01/2018	April-30-2023 (04/30/2023)	300000	6%	=RECEIVED(05/01/2018,04/30/2023,300000,6%,3) =428571.4286 =428571.43
May-01-2018(05/01/2018	April-30-2023 (04/30/2023)	500000	6%	=RECEIVED(05/01/2018,04/30/2023,500000,6%,3) =714285.7143 =714285.71

 

42) 

RRI:– This function calculates the periodic equivalent interest of an investment or any loan over a given period . It can also be used to calculate the Compound Annual Growth Rate (CAGR) in Excel.

It returns the rate for the same time unit in which we specify the number of periods. For example, if number of periods are expressed in quarters, RRI returns the quarterly equivalent interest rate.

Syntax:                

                 =RRI(nper, pv, fv)

·        Nper(required argument):–  The total number of periods over which the investment is made.

·        Pv(required argument):– Present value. The amount which we fixed or invested

·        Fv(required argument): Future value. This is the future value that is the goal of the investment

Eg: 

 

PV Investment (Present value)	Nper (Total number of period)	Future Value ( Maturity Amount)	RRI (Average Interest rate) =RRI(nper,pv,fv)
20000	4	25000	=RRI(4,20000,25000) =0.057371263 =0.057371263*100 =5.7%
15000	10	50000	=RRI(10,15000,50000) =0.127944873 =0.127944873*100 =12.79%
200000	10	550000	=RRI(10,200000,550000) =0.106453761 =0.106453761*100 =10.6%