Sunil K. Parameswaran - Fundamentals of Financial Instruments

The required function in Excel is PV. The parameters are:

Rate

Nper

Pmt

Fv

Type

Fv stands for the future value. The other parameters have the same meaning as specified for the FV function.

Sharon Oliver wants to accumulate $25,000 in her bank account after five years. The bank agrees to pay 5.40% per annum compounded quarterly. How much should she deposit today?

Allegra is offering an instrument that promises to pay $4,000 per year for 10 years, beginning one year from now. If the annual rate of interest is 5.40%, and interest is paid annually, what is the present value of the annuity?

We can use the PV function in Excel. The parameters are: Rate = 0.054, Nper = 10, Pmt = –4,000. There is no need to input parameters for Fv and Type. This is because there is no lump-sum terminal cash flow, and so there is no need to input a value for the future value. Type needs to be input only for annuities due.

The future value of this annuity may be computed using the FV function.

Now assume that the above annuities are annuities due. The present and future values may be computed as follows.

And

Lorraine has taken a loan of $500,000 which has to be paid back in eight annual installments. The interest rate is 4.80% per annum. The periodic installment can be computed using the PMT function in Excel. The parameters are:

Rate

Nper

PV

FV

Type

The values for PV and FV should have opposite signs.

For the first period,

Now consider the second period. There are two ways in which the PMT function can be invoked. We can specify the same set of parameters as for the first period. Or we can specify the Nper as 7, and the PV as the outstanding balance, which is $447,263.34.

Now consider the interest and principal components of each installment. We can use a function in Excel called IPMT to compute the interest component of an installment and another function called PPMT to compute the principal component of the installment. The parameters, for both, are

Rate: This is the periodic interest rate.

Per: This stands for period.

Nper: This represents the total number of periods.

Pv: This is the present value.

Fv: This is the future value.

Type: This has the usual meaning.

Consider the interest and principal components of the first installment.

. While computing the interest component of the second installment, we can invoke IPMT as IPMT(0.048,2,8,–500000) or as IPMT(0.048,1,7,–447263.94). Both will return a value of $21,468.64. Similarly, the principal component of the first installment is . For the second period,

IPMT and PPMT can be used with two sets of parameters. We can keep the total number of periods at the initial value, specify the present value as the initial loan amount, and keep changing Per to compute the interest and principal components. For the first installment, Per = 1, and for the n th installment, it is equal to n . The alternative is to re-amortize the outstanding amount at the beginning of each period over the remaining number of periods. Remember that each time we re-amortize, we are back to the first period. Thus, after every payment, we are back to the first period of a loan whose life is equal to the remaining time to maturity, and whose principal amount is equal to the remaining outstanding balance.

1 1To rephrase a famous Microsoft claim, in this case “What You See Is NOT What You Get.”

Текст предоставлен ООО «ЛитРес».

Прочитайте эту книгу целиком, купив полную легальную версию на ЛитРес.

Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.