# MORTGAGE ANALYSIS SHEET Help

#### Lylyjo

##### New Member
Hello everyone,

I am working on a project for class. I'm stuck on "Ensure the formulas for Year 1 can be copied correctly. Then copy each formula to the subsequent rows in the table. HINT: Your formulas are correct if the ending balance for year 30 is \$0. " I did follow all the steps but when it came to copy the formulas for each row, I'm getting errors and #VALUE! and "A value used in the formula is of the wrong data type." Please help. Thanks.

The steps are as follow which I followed

7. Setup an Amortization table on your worksheet similar to the one shown on the sample worksheet.
8. Enter the formulas below for the beginning balance row and year 1 row of the table.
Beginning Balance Row: Enter a formula in the Ending Balance cell to copy the Loan Amount value.
NOTE: The remaining cells on the Beginning Balance row should remain empty.
Interest Pymt for Year 1: Enter a formula to multiply the Ending Balance value on the previous row by the Annual Interest Rate value.
Principal Pymt for Year 1: Enter a formula to subtract the Annual Mortgage Payment value and the Interest Pymt value for year 1.
Ending Balance for Year 1: Enter a formula to subtract the Ending Balance value on the previous row and the Principal Pymt value for year 1.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Check the formula that calculates annual interest. The reference to the cell with the interest rate needs to refer to absolute reference. If the interest rate is in A1 the formula should to \$A\$1 not just A1. Same thing to check with the reference to the annual payment cell.

"[....] HINT: Your formulas are correct if the ending balance for year 30 is \$0. "
[....]
The steps are as follow which I followed
[....]
8. [....]
Principal Pymt for Year 1: Enter a formula to subtract the Annual Mortgage Payment value and the Interest Pymt value for year 1.
Ending Balance for Year 1: Enter a formula to subtract the Ending Balance value on the previous row and the Principal Pymt value for year 1.

konew1 probably addressed your problem with the #VALUE error. No pun intended.

But I wanted to offer a heads-up about some other potential misunderstandings.

First, it is not uncommon for the ending balance not to be exactly zero. If the cell is formatted as General, the ending balance might appear as something like 1E-16 or even -1E-16. The latter might look like (\$0.00) if the cell is formatted as Accounting with 2 decimal places.

The work-around is to format the cell as Number or Currency with 2 decimal places and to avoid the (...) form for displaying negative numbers.

Second, "subtract this and that" is ambiguous. The correct instructions are:

• Principal Pymt for Year 1: Enter a formula to subtract the Interest Pymt value for year 1 from the Annual Mortgage Payment value.
• Ending Balance for Year 1: Enter a formula to subtract the Principal Pymt value for year 1 from the Ending Balance value on the previous row.

Thanks everyone for your help. I'm still having issues with this project for school. I still can't copy B6 to the other cells it's giving me an error \$- The formula in this cell refers to cells that are currently empty. Here's a picture of the excel file that I'm working on. Again thank you to all.

http://i58.tinypic.com/33wrya0.png

Thanks everyone for your help. I'm still having issues with this project for school. I still can't copy B6 to the other cells it's giving me an error \$- The formula in this cell refers to cells that are currently empty. Here's a picture of the excel file that I'm working on. Again thank you to all.

http://i58.tinypic.com/33wrya0.png

For Beginning Balance Row: Enter a formula in the Ending Balance cell to copy the Loan Amount value. I entered =H8
For Interest Pymt for Year 1: Enter a formula to multiply the Ending Balance value on the previous row by the Annual Interest Rate value. I entered this formula =PRODUCT(D15*\$F\$6)
For Principal Pymt for Year 1: Enter a formula to subtract the Annual Mortgage Payment value and the Interest Pymt value for year 1. I entered this formula =SUM(H9-B16)
For Ending Balance for Year 1: Enter a formula to subtract the Ending Balance value on the previous row and the Principal Pymt value for year 1. I entered this formula =SUM(D15-C16)

Ensure the formulas for Year 1 can be copied correctly. Then copy each formula to the subsequent rows in the table.
HINT: Your formulas are correct if the ending balance for year 30 is \$0.

Replies
6
Views
286
Replies
9
Views
389
Replies
2
Views
168
Replies
13
Views
421
Replies
3
Views
367

1,203,488
Messages
6,055,716
Members
444,811
Latest member
NotJack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back