MORTGAGE ANALYSIS SHEET Help

[Lylyjo]

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.

 

[AhoyNC]

Maybe this link will help.

https://www.youtube.com/watch?v=WAX4OAHQmzE&index=1&list=PL1C040B9C3D6B319B

 

[konew1]

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.

 

[joeu2004]

"[....] 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.

 

[Lylyjo]

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

 

[Lylyjo]

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.