MORTGAGE ANALYSIS SHEET Help

Lylyjo

New Member
Joined
Sep 1, 2014
Messages
4
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
"[....] 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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,395
Messages
6,178,355
Members
452,841
Latest member
GenAkaman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top