• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Excel mortgage calculator

Status
Not open for further replies.
P

Pandamonium

Golden Member
This is probably more for my curiosity at this point, but it still nags me that I can't figure this out.

Basically, I want to make an excel file that can answer all the questions I could think to ask a loan officer. So if you input the values you know, Excel computes all the other values. It's the kind of thing that I frequently see in online JavaScript-based calculators.

So if I enter my max PITI, property taxes, homeowners insurance, purchase price, and down payment, I want excel to tell me what my actual loan amount will be. If my down payment is insufficient, I want to know the shortfall. And if my down payment is excessive, I want to know what my actual PITI is. I'd also like to know what down payment is optimal to keep my payments at the max PITI.

I also want to be able to adjust the interest rate, purchase price, asking price, etc on the fly.

And the trickiest part of this: for some values, like the down payment, I want to be able to enter it as a dollar amount or as a percentage of the purchase price amount. How do I do this?
 
I have a spreadsheet at home that does this. Forgot where I got it, but I use it for my mortgage.

I can provide later if you want. Just PM me.
 
It's pretty easy to set up. The function you need is PMT, which enables you to figure out your monthly payments given rate, term, and loan amount.

PMT(rate, nper, pv, [fv], [type])

Rate - interest rate
nper - total number of payments
pv - loan amount

For example, if you have a 30 year fixed rate at 4% mortgage loan of $200K, then =PMT((4%/12),360,-200000) would give you your monthly payment.

All of the other monthly payments besides your mortgage should be simple arithmetic...
Make sure you factor in closing costs & any money that you need to set aside for renovations, moving costs, etc when you figure out your downpayment vs mortgage amount
 
If you want a file, I can send you one. PM me your email address...I made one on Excel to show exactly that. (Just moved in Tuesday) You can put down offer price, % of down payment, loan terms, taxes, insurance, etc. It'll also take into account interest paid over life of loan, etc.
 
I have something like this, but didn't compute PMI or insurance, etc... It's basically a simple calculator that caluculates payments. I then took the results column and copied it 5 more times and incremented the loan amount by $5000 or $10000 to give me different home values to help figure out what the differentials would be.

I created a similar spreadsheet that incremented the APR and length of the mortgage on the loans too. That allows me to better judge how much different loan terms can affect the overall cost of the mortgage. I recommend looking at Credit Unions that do in-house loans. Mine actually has 5,10,15, and 20 year in-house loans....they outsource their 30 year loans....so I automatically dropped 30 year mortgages from my search to snag a better APR and to keep it in-house.
 
Thanks for all the suggestions and the file, Raging. I guess I'm not looking so much for a payment calculator/amortization calculator as I am for something to answer questions like:
"how big a loan can I obtain?"
"how much would I need to supply for a downpayment to obtain a home more expensive than my max loan amount?"

Basically we'll be a dual income (roughly equal incomes) family buying with a solo qualified physician mortgage. So 0% down, no points, no PMI. Despite being risk averse, we don't have a problem pushing the limits of what is normally "affordable" because my income isn't being factored in. It will not be my primary residence until ~July 2014, so while most of my income will actually be tied up in living expenses, all of my discretionary income will be available for the first year.

The problem is that the bank has determined our maximum monthly payment, and the loan amount is a little short of the house we want to buy. We can adjust interest rates (depending on 5/1, 7/1, 10/1, 30y), and continue to solicit quotes for homeowners insurance to increase our loan amount. But aside from that, I want to know how much we would need to have for a downpayment to make this happen. I also want to know how much of a downpayment we would need to have in order to switch other aspects of the mortgage (ie: go from a 7/1 to 30y). But I want to have this be as dynamic a calculator as possible.

I've done most of the math already, so I don't know how useful this would be to me at this time. But I hope to make something functional enough that we could provide it to our LO to provide to future clients to save everyone some time and uncertainty. And in the future, I could also use this spreadsheet to recalculate things for the next time we move.
 
Pandamonium said:
Thanks for all the suggestions and the file, Raging. I guess I'm not looking so much for a payment calculator/amortization calculator as I am for something to answer questions like:
"how big a loan can I obtain?"
Click to expand...

It's not how big of a loan you can obtain.

It's how big of a loan SHOULD You obtain with your income.

General rule of thumb is your mortgage (PITI) should not exceed 30% of your income.


Pandamonium said:
"how much would I need to supply for a downpayment to obtain a home more expensive than my max loan amount?"
Click to expand...


DO NOT let your bank tell you what your loan can be. What they are willing to give is NOT what you SHOULD get.

This is exactly what got us into the Mortgage mess.

YOU need to decide what you can/can't afford. NO ONE else can do that.

In general, I would recommend having 20% down payment on your home.

Pandamonium said:
Basically we'll be a dual income (roughly equal incomes) family buying with a solo qualified physician mortgage. So 0% down, no points, no PMI. Despite being risk averse, we don't have a problem pushing the limits of what is normally "affordable" because my income isn't being factored in. It will not be my primary residence until ~July 2014, so while most of my income will actually be tied up in living expenses, all of my discretionary income will be available for the first year.

The problem is that the bank has determined our maximum monthly payment, and the loan amount is a little short of the house we want to buy. We can adjust interest rates (depending on 5/1, 7/1, 10/1, 30y), and continue to solicit quotes for homeowners insurance to increase our loan amount. But aside from that, I want to know how much we would need to have for a downpayment to make this happen. I also want to know how much of a downpayment we would need to have in order to switch other aspects of the mortgage (ie: go from a 7/1 to 30y). But I want to have this be as dynamic a calculator as possible.

I've done most of the math already, so I don't know how useful this would be to me at this time. But I hope to make something functional enough that we could provide it to our LO to provide to future clients to save everyone some time and uncertainty. And in the future, I could also use this spreadsheet to recalculate things for the next time we move.
Click to expand...

You would need the difference + 3-5% for closing fees.

Say the house is 300k and you got approved 250. You will need about 50k+ 3% of the total house cost.
 
Pandamonium said:
Thanks for all the suggestions and the file, Raging. I guess I'm not looking so much for a payment calculator/amortization calculator as I am for something to answer questions like:
"how big a loan can I obtain?"
"how much would I need to supply for a downpayment to obtain a home more expensive than my max loan amount?"

Basically we'll be a dual income (roughly equal incomes) family buying with a solo qualified physician mortgage. So 0% down, no points, no PMI. Despite being risk averse, we don't have a problem pushing the limits of what is normally "affordable" because my income isn't being factored in. It will not be my primary residence until ~July 2014, so while most of my income will actually be tied up in living expenses, all of my discretionary income will be available for the first year.

The problem is that the bank has determined our maximum monthly payment, and the loan amount is a little short of the house we want to buy. We can adjust interest rates (depending on 5/1, 7/1, 10/1, 30y), and continue to solicit quotes for homeowners insurance to increase our loan amount. But aside from that, I want to know how much we would need to have for a downpayment to make this happen. I also want to know how much of a downpayment we would need to have in order to switch other aspects of the mortgage (ie: go from a 7/1 to 30y). But I want to have this be as dynamic a calculator as possible.

I've done most of the math already, so I don't know how useful this would be to me at this time. But I hope to make something functional enough that we could provide it to our LO to provide to future clients to save everyone some time and uncertainty. And in the future, I could also use this spreadsheet to recalculate things for the next time we move.
Click to expand...

VDub hit the points on the head already. Simply put, that's up to the lender to determine that. A spreadsheet or online calculator can give you a rough number. If your bank isn't giving you what you want, hey, you should probably listen to reason. If you want to override their financial expertise and say that you can afford more - go get quotes from other lenders. Your bank isn't the only option available. If you know of anyone in real estate - ask them for their preferred lenders.

We were approved for about $225K more than the house we actually bought. If they gave you a max number and that's still not enough, then you are probably going to be living paycheck to paycheck...that's not something you want to do. By the sounds of physician, sure, the expectation is to make bank...but there's no guarantee that what you'll make in 20 years is the kind of house you can afford NOW.
 
Vdubchaos said:
It's not how big of a loan you can obtain.

It's how big of a loan SHOULD You obtain with your income.

General rule of thumb is your mortgage (PITI) should not exceed 30% of your income.
Click to expand...

Two ways of looking at this rule, related but not identical: the total amount that you spend on housing per year (12*monthly PITI payment) shouldn't exceed 30% of yearly income, and the total VALUE of the loan shouldn't exceed 3 * yearly income (although a lot of people stretch this to 4 or even 5x, but you probably shouldn't do that unless you get a really low rate, pay very low property taxes, and expect your income to increase over the next several years).
 
I appreciate the feedback (honestly- I'd echo most of these statements myself); and I'd like to clarify that I'm not looking for financial advice. Our particular situation is something of an edge case that conventional wisdom doesn't fully cover. What I am looking for is a way to make Excel do more plugging and chugging so that I can better model my model finances.

I'm frequently one of the first people to pipe up and declare "you're not special, etc". So here is a brief explanation of why I believe we are an edge case.

If both of our incomes could be used in this financing, we would be well below nearly all standard measures of affordability. The fact that we are being qualified with one income only is the stumbling block. If we were to wait a year so that both of our incomes could be used, the financial part of buy vs rent becomes unclear because we will complete training in 3 yrs (4 if we pursue fellowship in the area). The loan we are obtaining is itself a special loan. Few banks offer them and that they are not available in every state. The physician loan is the last no/low down loan out there, and it is still profitable because of historically low physician default rates. With it, we can obtain a loan that we could not otherwise obtain (student loan debts are excluded from DTI calculations), and banks sell us on a marginally more expensive mortgage.
 
Not sure if I really understand the question. You know how much you have in the bank and therefore how much you can put down. The question becomes how big of a loan you can get from your financial institution, which would then drive the maximum property price that you can pay (downpayment + loan + closing cost). Correct me if I'm wrong, but no Excel model that you build is going to change a financial institution's mind when they decide what kind of loan they will give to you.

Raging's spreadsheet lets you determine your PITI based on the set of parameters laid out above, which you can then use to project your cash flow situation over however length of period you want.

What exactly is still missing that would allow you to do what you wanted?
 
Pandamonium said:
I appreciate the feedback (honestly- I'd echo most of these statements myself); and I'd like to clarify that I'm not looking for financial advice. Our particular situation is something of an edge case that conventional wisdom doesn't fully cover. What I am looking for is a way to make Excel do more plugging and chugging so that I can better model my model finances.

I'm frequently one of the first people to pipe up and declare "you're not special, etc". So here is a brief explanation of why I believe we are an edge case.

If both of our incomes could be used in this financing, we would be well below nearly all standard measures of affordability. The fact that we are being qualified with one income only is the stumbling block. If we were to wait a year so that both of our incomes could be used, the financial part of buy vs rent becomes unclear because we will complete training in 3 yrs (4 if we pursue fellowship in the area). The loan we are obtaining is itself a special loan. Few banks offer them and that they are not available in every state. The physician loan is the last no/low down loan out there, and it is still profitable because of historically low physician default rates. With it, we can obtain a loan that we could not otherwise obtain (student loan debts are excluded from DTI calculations), and banks sell us on a marginally more expensive mortgage.
Click to expand...

Honestly...if you have to rely just on the one vs both incomes...maybe renting or buying a starter house is the way to go for now. It sucks, yes, but it might be your best option.

We closed last Thursday and moved in Tuesday. Both my fiancee and I were on the financing before (long story short) she had to be removed b/c of a recent ding to her credit. We still qualified for the house and loan just using my income, but was pretty close.

In your case - buying a house could hurt you guys...if for some reason you were to get some phenomenal job offer at a hospital cross country...you'd be stuck with a home in a different city. If you tried to sell, you'll get the shaft and lose money off of it. Then again, if you really must have the house at a certain price tier/range, then save up a bit more money and put more down up front, or ask around for various lenders. If you are on the cusp, then there's probably some other lender who will work with you to get you a loan.
 
Status
Not open for further replies.

TRENDING THREADS

Back
Top