Help w/ Mortgage Math on Excel

bright

New member
Joined
Mar 12, 2008
Messages
4
This is for a real estate principles course, mortgage math on excel.

Directions: Using excel, create a mortgage math calculator. Put your inputs at the top & set up the spreadsheet so that you can change the inputs (loan amount, interest rate, & loan term) & your outputs will show the correct answers without modifying any formulas. You can assume that payments will always be on a monthly basis.
Turn in printouts of:
1. the inputs & outputs
2. formulas


Inputs
Purchase Price $400,000
Percent Down 20%
Discount Points 1
Term 30 yrs
Rate 6%
Loan Amt $320,000
Holding Period 5 yrs

Outputs
Payment $1,918.56
Balance $297,773.94
Effective Interest Rate 6.24%



Above are the inputs & outputs...got to figure out how (formulas) I got the outputs from the inputs.

Thank you for any help!
 
bright said:
This is for a real estate principles course, mortgage math on excel.

Directions: Using excel, create a mortgage math calculator. Put your inputs at the top & set up the spreadsheet so that you can change the inputs (loan amount, interest rate, & loan term) & your outputs will show the correct answers without modifying any formulas. You can assume that payments will always be on a monthly basis.
Turn in printouts of:
1. the inputs & outputs
2. formulas


Inputs
Purchase Price $400,000
Percent Down 20%
Discount Points 1
Term 30 yrs
Rate 6%
Loan Amt $320,000
Holding Period 5 yrs

Outputs
Payment $1,918.56
Balance $297,773.94
Effective Interest Rate 6.24%



Above are the inputs & outputs...got to figure out how (formulas) I got the outputs from the inputs.

Thank you for any help!

What were you taught about these formulae in the class?

Did you do a google search?
 
I got the 1st Output correct of Payment 1,918.56 with the following:
=PMT(6%/12,360,-320000)


....but the other two I'm still off. So far I have:

Balance: =PV(6%/12,30,-10666.67) Resulting in 296,470.00 instead of the 297,773.94
&
Effective Interest Rate: =RATE(360,-10666.67,296470) Resulting in 4% instead of the 6.24%

For Balance I am using the PV (Present value) function & for Effective Interest Rate I'm using RATE.


Can anyone see where I'm a little off on the last two and fix it up? Thanks again
 
to do this I select the cell to contain the result, go to the insert menu, select function, there is a drop down box that lets you select the category, select Financial, then pick the function that matches the needs.....


...but I'm still off a little on the other 2, anyone? Much appreciated
 
Top