Excel Help: Assignment 20 (Chapter 6, Level 2 Case)Mortgage Calculator

Instructions:

Show all formula work for each

Fill in the mortgage calculator using the followinginformation:

Mortgage Amount: $270,000

Duration in Years: 30 years

Interest Rate: 5.75%

Payments per year: 12

Calculate the periodic payment using the PMT function. Round tothe nearest cent.

Complete the amortization table as follows:

Fill in the periods (12 payments per year for 30 years)

In cell B12, enter the beginning principal by capturing theamount in cell B4.

In cell C12, use the IPMT function to calculate the InterestPayment.

In cell D12, use the PPMT function to calculate the PrincipalPayment.

In cell B13, enter a formula to calculate the remainingprincipal for period 2.

Copy cell B13 down the column. Copy cells C12 and D12 down thecolumn.

Click on the Tax worksheet.

Complete the table as follows:

Column A: Enter periods 1-30

Column B: Use the CUMIPMT function to calculate the cumulativeinterest for each year

Cell C3: Enter a formula that will calculate the tax deductionfor period 1. Your formula should copy both down the column andacross the row.

Click on the Depreciation worksheetand enter the following test data:

Asset Value: $190,000

Salvage Value: $8,000

Life: 20 years

In cell C5, calculate the straight line depreciation.

Complete the 2^nd table as follows:

Fill in 20 years in column A.

Calculate the depreciation in column B using the doubledeclining balance method (DDB function)

Calculator Sheet

Tax Sheet

Depreciation Sheet

State Savings andLoan

Mortgage Calculator - PaymentValue

Mortgage Amount

Duration in Yrs

Payments/yr

Interest Rate/yr

Periodic Payment

Period Number

Remaining Principal

Interest Payment

Principal Payment

15% Rate

28% Rate

33% Rate

Year

Cumulative Interest For Year

TAX

TAX

TAX

DepreciationCalculation
Asset Value:
Salvage Value:
Life:
Straight LineDepreciation:
Year:	DDB

A 1 2 Mortgage Calculator - Payment Value 3 4 Mortgage Amount 5 Duration in Yrs 6 Payments/yr 7 Interest Rate/yr 8 Periodic Payment 9 10 B State Savings and Loan 11 Period Number Remaining Principal Interest Payment D Principal Payment