Monthly Mortgage payment to Interest Rate/Factor

[Fan of MS Excel]

Good Morning MS Excel masters.... I bow to you.

I'm a homebuyer educator. It is my goal to teach people how to select a house they can afford and how their accumulated debt, score, and down payment (cash) can impact how much house they can afford/buy.

I've built a user friendly spreadsheet to place on a flash drive for our home buyer participants. I have one gap. I want to show how the monthly "monthly mortgage payment" changes when the "mortgage rate" changes. For example, for a loan amount with an interest rate of 4.5% for 30 years, there is an interest rate factor of 5.07%. To get a monthly mortgage payment, you multiply the loan amount (*) the corresponding interest rate factor.

My new homebuyers are not very MS Excel savvy. I will exposing some of them to the MS Excel for the very first time. I want to start very simple. My challenge:

Every time the mortgage interest rate changes, I need the accompanying "interest rate factor" linked to the "monthly mortgage payment" formula to change automatically with the interest rate. At present, when the interest rate changes, the participants have to go to a separate worksheet, select the appropriate interest rate factor associated with that interest rate, and input it into the monthly mortgage payment formula. This process can result in errors if the user does not know how to edit a formula properly.

My current monthly Mortgage Payment formula reads: =C11*'Int Rate Factor'!D9
C11 is the loan amount; D9 refers to the separate worksheet that list interest rates and their factors. Through the Data Validation process, I created a cell (B7) that contains a drop box of mortgage interest rates. These interest rates are located on a separate worksheet. I have their corresponding interest rate factors located in an adjacent column. For a loan with a 4.5 interest rate, the 4.5% is located on a separate worksheet in B9 and its corresponding 30 year interest factor of 5.07% located in cell D9.

My "knowledge gap" is how to develop a formula: when the interest rate changes in cell B7, it's corresponding 30 year interest factor will be multiplied by the loan amount in C11 to produce an "annual mortgage amount" in C12. Can anyone assist?

 

[json]

I'm not sure if i'm following completely, but it sounds like a vlookup would solve your issue.

try this:
=C11*(VLOOKUP(B7,'Int Rate Factor'!B6:D19,3,FALSE))

 

[json]

To explain what that should do:
like it previously did, it will look at the value in C11.. .then 'the magic' happens
The VLOOKUP Function:
syntax: =VLOOKUP(LookupValue, TableArray, Col.Index,True/False)
The LookupValue is used to set what value or which cell contains the value that you're looking for
The TableArray is used to specify the location of the data that you want the formula to search in
The Col.Index is used to set which column number in the TableArray contains the data that you're looking for.
True/False: You will specify True or False (can also use 0 or 1). True will search for an approx. match while False will search for an exact match. (False is likely used more often than True, especially when search for an exact value)

Formula Breakdown!!! ( in game show host voice/yell accompanied with typical game show-like music)
=Vlookup(B7,'Int Rate Factor'!B6:D19,3,False)
B7 -- this cell contains the mortgage rate that we want to find the 30yr factor value for
'Int rate Factor'!B6:d19 -- this is telling the formula to look at the Int Rate Factor sheet in cells B6 through D19
3 -- This says that when you find the value of B7 in the tableArray, return the content from the 3 column
False -- Search for an exact match of B7 in the TableArray

=Vlookup(4.50%,'Int Rate Factor'!B6:D19,5.07%,False) This formula will return: 5.07%

So your original formula =C11*(VLOOKUP(B7,'Int Rate Factor'!B6:D19,3,FALSE)) will work into: =C11*5.07% (the value that the vlookup formula returned)

The beauty is if the mortgage rate in B7 changes, the vlookup will return the new 30yr Factor.



EDIT: Some other suggestions:
- You said that you are providing this spreadsheet to your clients via a flash drive... are they keeping this?
- It may be helpful to Hide the Int Rate Factor Sheet so that they don't see and are not tempted to inspect it and potentially change something accidentally if they are not familiar with Excel as you say.
- Maybe you already have this in the actual spreadsheet, but it may also be helpful/safer to protect your sheet and only leave the cells that they need to have access to unlocked. that way they can't mess up a formula accidently.
If you need help with any of this, just let me know!!
I hope this helps!

 

[Luke M]

Since it's a sorted list, don't even need all of a VLOOKUP's power, a LOOKUP will do.
=C1*LOOKUP(B7,'Int Rate Factor'!B6:B19,D6:D19)

Also, you might want to check out the built-in Amortization Template. If you right-click on a worksheet tab, Insert, go to "Spreadsheet solutions" tab, you can find an Amortization schedule there. Could give you some other ideas for dashboard design. Ideas you might want to include:
-I notice that your template doesn't let user change duration of loan
-Color code the input cells, clearly let user know where to go
-You can use Data Validation to provide Input message, letting user know more about what they should enter in a cell.

 

[Luke M]

I'm not sure your interest rate table is correct. You can use the EFFECT function to calculate effective interest rate, which returns different values then what you have. Along that line, you could use the PMT function to calculate payment amount, skipping having to figure out effective interest rate altogether.

 

[cdbauer1]

@Fan of MS Excel why would you use Gross income rather than net after-tax income? This gives the impression that they're taking home a lot more money to spend on their home than they actually are but maybe that's an industry standard I'm not familiar with.

 

[json]

@Fan of MS Excel why would you use Gross income rather than net after-tax income? This gives the impression that they're taking home a lot more money to spend on their home than they actually are but maybe that's an industry standard I'm not familiar with.

good point since we all know that the government likes to take their part of the pie

 

[cdbauer1]

Also, are you sure this tool isn't redundant? There have to be thousands of existing online calculator tools out there you could use that are maintained by others. Just a thought.