Excel Help: Assignment 20 (Chapter 6, Level 2 Case) Mortgage Calculator Instructions: Show all…

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 following information: 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 to the 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 the amount in cell B4. In cell C12, use the IPMT function to calculate the

»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 following information: 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 to the 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 the amount in cell B4. In cell C12, use the IPMT function to calculate the Interest Payment. In cell D12, use the PPMT function to calculate the Principal Payment. In cell B13, enter a formula to calculate the remaining principal for period 2. Copy cell B13 down the column. Copy cells C12 and D12 down the column. 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 cumulative interest for each year Cell C3: Enter a formula that will calculate the tax deduction for period 1. Your formula should copy both down the column and across the row. Click on the Depreciation worksheet and 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 2nd table as follows: Fill in 20 years in column A. Calculate the depreciation in column B using the double declining balance method (DDB function) Calculator Sheet Tax Sheet Depreciation Sheet State Savings and Loan Mortgage Calculator – Payment Value 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 Depreciation Calculation Asset Value: Salvage Value: Life: Straight Line Depreciation: Year: DDB

»