account 2301

Requirement 1

Complete the data table. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.

DATA

Loan Amount

Interest Rate

Periods

Requirement 2

Using the present value of an ordinary annuity table, calculate the payment amount and complete the amortization schedule.  Enter all amounts as positive values. Do not use a minus sign or parentheses for any values. Use the effective interest amortization method.

(Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong.)

a.      Calculate the loan payment by dividing the loan amount by the appropriate present value factor.

b.      Round values to two decimal places.

  Calculate the interest expense in the third year as the loan payment minus the loan balance at the beginning of the third year.

c.      Use absolute cell references and relative cell references in formulas. 

         Use absolute cell references only to cells C6 and C19 for interest expense and payment calculations.

Payment (using PV table)

Present Value of an Ordinary Annuity of $1

Period

Beginning Balance

Principal Payment

Interest Expense

Total Payment

Ending Balance

6%

8%

10%

0

1

0.9434

0.9259

0.9091

1

2

1.8334

1.7833

1.7355

2

3

2.6730

2.5771

2.4869

3

4

3.4651

3.3121

3.1699

Total

5

4.2124

3.9927

3.7908

Requirement 3

Using the Excel PMT function, calculate the payment amount and complete the amortization schedule. 

Use the effective interest amortization method.

(Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong.)

a.       The PMT function calculates a payment amount that results in a negative number. 

Reverse this to a positive number for calculations in the amortization schedule.

b.       Round values to two decimal places.

Calculate the interest expense in the third year as the loan payment minus the loan balance at the beginning of the third year.

c.        Use absolute cell references and relative cell references in formulas. 

       Use absolute cell references only to cells C6 and C39 for interest expense and payment calculations.

Payment (using PMT function)

Period

Beginning Balance

Principal Payment

Interest Expense

Total Payment

Ending Balance

0

1

2

3

Total

HINTS

Cell | Hint:

C19 | Use the function =ROUND( ) to calculate the loan payment.

F23:F25 | Use absolute cell reference for total payments in periods 1-3. (Absolute cell reference uses $, example $A$1)

D23:E24 |   Use the function =ROUND( ) to calculate the interest expense and payment for the first and 

                     the second periods.

D26 | Use the function =SUM( ) to calculate the total principal payment.

C39 | Use the function =ROUND( ) and the function  =PMT( ) to calculate the loan payment. The PMT 

           function uses the interest rate, the number of periods, and the loan amount (in that order). Do not 

           enter any value for the FV or TYPE arguments of the PMT function.

F43:F45 | The payment must be a positive value.

* Whenever using the ROUND function, use the function =Round( ,2) to calculate the loan payment to two decimal places.