## Financial functions: price, pricedisc, pricemat, pv, rate

#### Excel Function PRICE

The PRICE() function is used to calculate the price per face value of \$100 of a security paying interest.

This requires that the interest paid by the security is periodic.

The Syntax of the function is as follows:

PRICE(settlement,maturity,rate, yield,redemption,frequency, basis)

 Settlement Date Maturity Date Rate Yield Redemption Payment mode PRICE() Formula 01/02/2017 31/12/2018 0.08 0.1 100 2 96.5790546044462 =PRICE(L4,M4,N4,O4,P4,Q4) 01/03/2017 31/07/2018 0.02 0.15 200 1 166.49507537164 =PRICE(L5,M5,N5,O5,P5,Q5) 20/07/2017 01/04/2018 0.06 0.1 150 4 144.007668918963 =PRICE(L6,M6,N6,O6,P6,Q6) 01/03/2018 09/09/2018 0.09 0.12 300 1 286.47791718946 =PRICE(L7,M7,N7,O7,P7,Q7)

#### Excel Function PRICEDISC

The PRICEDISC() function calculates the price of a discounted security per \$100 face value.

This function is similar to the PRICE() except that, this calculates the discounted price.

The Syntax of the function is as follows:

PRICE(settlement,maturity,discount rate,redemption,basis)

 Settlement Date Maturity Date Discount Redemption Basis PRICEDISC() Formula 01/02/2017 31/12/2018 0.08 100 1 84.7013698630137 =PRICEDISC(L16,M16,N16,O16,P16) 01/03/2017 31/07/2018 0.02 200 2 194.255555555556 =PRICEDISC(L17,M17,N17,O17,P17) 20/07/2017 01/04/2018 0.06 150 3 143.712328767123 =PRICEDISC(L18,M18,N18,O18,P18) 01/03/2018 09/09/2018 0.09 300 4 285.9 =PRICEDISC(L19,M19,N19,O19,P19)

#### Excel Function PRICEMAT

The PRICEMAT() function calculates the price of a security that pays interest at maturity.

This function also takes into account, a face value of \$100.

The Syntax of the function is as follows:

PRICE(settlement,maturity,issue,rate,yield,basis)

 Settlement Date Maturity Date Issue Date Rate Yield Basis PRICEMAT() Formula 01/02/2017 31/12/2018 01/01/2017 0.1 100 2 -0.244092867097428 =PRICEMAT(L28,M28,N28,O28,P28,Q28) 01/03/2017 31/07/2018 01/03/2016 0.15 200 1 -14.5207439888209 =PRICEMAT(L29,M29,N29,O29,P29,Q29) 20/07/2017 01/04/2018 20/07/2016 0.1 150 4 -8.89213364903973 =PRICEMAT(L30,M30,N30,O30,P30,Q30) 01/03/2018 09/09/2018 01/03/2016 0.12 300 1 -23.1569677186028 =PRICEMAT(L31,M31,N31,O31,P31,Q31)

#### Excel Function PV

The PV() function is used to determine the present value of an investment.

The calculation is based on a series of future payments.

The Syntax is as follows:

PV(rate,no. of periods, payment per period, future value, type [0 or 1])

 Rate No. of periods Payment per period future value type PV() Formula 0.08 60 1000 0 0 -49318.4333356258 =PV(L40,M40,N40,O40,P40) 0.1 60 1500 1 1 -71186.9784370162 =PV(L41,M41,N41,O41,P41) 0.14 60 2000 2 0 -85955.0301444769 =PV(L42,M42,N42,O42,P42) 0.095 60 2200 3 1 -105583.780863498 =PV(L43,M43,N43,O43,P43)

#### Excel Function RATE

The RATE() function is used to calculate the interest rate payable for a specified loan amount.

This function can also be used to calculate the rate for achieving a target investment amount.

The calculation is performed over a certain defined period.

The Syntax is:

Rate(no.of periods, payment per period, present value, [future value],[type],[guess])

The last three parameters are optional.

 No. of periods Payment per period Present value future value type RATE() Formula 60 1000 50000 0 0 6.18341316125377E-03 =RATE(L52,-M52,N52,O52,P52) 60 1500 60000 1 1 1.49703649906816E-02 =RATE(L53,-M53,N53,O53,P53) 60 2000 45000 2 0 4.02892879025226E-02 =RATE(L54,-M54,N54,O54,P54) 60 2200 100000 3 1 9.9575220332307E-03 =RATE(L55,-M55,N55,O55,P55)