Purpose:

This financial model allows you to generate the amortization table for any amount of principle you may borrow. The spreadsheet computes, for each period, the amount of each payment, the interest component, the principal component, and the balance of the principle amount.

Design:

The model should be set up to handle as many amortization periods as you may desire, provided that you extend the formulae accordingly. It should handle any frequency of compounding: daily, weekly, monthly, quarterly, or annually. For purposes of this assignment, we shall use monthly compounding.

Required:

1.In cell B15, use a PMT function to calculate the payment amount.

2.In cell B16, use the IF function to display zero if the amount in cell E15 (Principle balance of the previous period) is less than 0.000001 (1E-5). Thus implying a zero because some functions may bring problems with zero.

3.In cell C15, use an IPMT function to calculate the amount of interest paid in the period specified in cell A15.

4.In cell C16, use the IF function to return zero amount if the principal balance for the previous payment is less than 0.000001.

5.In cell D15, use a PPMT function to calculate the amount of principal paid in the period specified in cell A15.

6.In cell D16, use a function almost similar to that in cell D15 - with improvements such as zero amount will result if the principal balance for the previous payment is less than 0.000001.

7.In cell E15, insert a formula that calculates the amount of principal outstanding after one payment.

8.In cell E16, insert a formula (using the IF function or otherwise) that calculates the amount of principal outstanding after the payment made in the current period. A zero amount should result if the principal balance of the previous period is less than 0.000001

9.The formulae in Row 16 if correctly entered should be copied downwards to as many rows as required by the number of payments.

Neuron Systems a company dealing in medical appliances borrows $10 million at an interest of 21.5% per annum from a local bank. This amount should be repayable in equal monthly installments over a period of ten years.

Use Excel spreadsheet