Excel assignment 2

BUS 363, Assignment 2, Due Date: on Syllabus

You may submit this assignment using the iLearn assignment link.

Part 1: Loan Affordability Analysis

Create a monthly payment table with changing interest rate and loan amount and allow users to

change the term and affordable payment as below and use the conditional formatting to highlight

the area of rate/loan that is affordable (less than or equal to the affordable payment) with green

color and area that is not affordable with red color.

Part 2: Redo assignment 1, Part 2. Use the PMT, IPMT, PPMT and CUMIPMT to

compute the monthly payment, interest payment, principal payment and cumulative

interest payment. Note that the layout of the table is slightly different from assignment 1.

There is no payment period 0 and there is an added Total Interest Payment column to

compute the running total of the interest payment.

Part 3: Double Declining Depreciation Table

For tax purpose an item may be depreciated over a period of several years N. With the

double-declining balance method of depreciation, each year the item depreciates by: value

at the beginning of that year * 2/N. For a $2000 value item of 5 years life, the first year’s

depreciation is calculated as: 2000*2/5. In the last year it is depreciated by its value at the

beginning of the last year. Create a double-declining depreciation table as shown below

and test your spreadsheet with property value = 2000 and life = 5. Use an IF function to

compute each year’s depreciation.