• About Me
  • Courses
  • Contact
    • Ads
  • Press & Media
  • Free eBook
  • Free Facebook Group
  • Disclosures
    • Privacy
Yes, I Am Cheap
Plans starting at $15 /month & Phones starting at $99
ADVERTISEMENT
  • Home
  • News
    • All
    • Politics
    stimulus

    Why You Might Not Get A Stimulus Payment

    Credit card security, equifax data breach

    How to Claim $125+ from the Equifax Data Breach Settlement

    foreclosure homes - When Does It Make Sense to Refinance?

    When Does It Make Sense to Refinance?

    no apple

    Get A New iPhone? Heck No!

    poor definition

    Mom Fired For Working At McDonald’s While Kid Plays In Park…Was She Wrong?

    What’s Keeping You From Finding A Job

    U.S. Students Fall Behind: Seemingly Get Dumber By The Minute

    Trending Tags

    • Trump Inauguration
    • United Stated
    • White House
    • Market Stories
    • Election Results
  • Lifestyle
    • All
    • Food
    • Health
    free thanksgiving dinner

    How to Get Your Thanksgiving Meal for Free

    using hand sanitizer, your own

    How to Make Your Own Hand Sanitizer

    Yoga pose

    5 Ways to Practice Yoga on a Budget

    Traveling, new cultures, Start a Saving Challenge, Spend Smart on Food

    3 Simple Ways to Save for A Vacation

    spending money, debt, Take a Roommate, Get a Smaller Home

    Lifestyle Changes To Pay Down Debt Faster

    Trending Tags

    • Golden Globes
    • Game of Thrones
    • MotoGP 2017
    • eSports
    • Fashion Week
  • Investing
  • Make Money
  • Learn Something
  • News
  • Reduce Debt
  • Rental Property
No Result
View All Result
  • Home
  • News
    • All
    • Politics
    stimulus

    Why You Might Not Get A Stimulus Payment

    Credit card security, equifax data breach

    How to Claim $125+ from the Equifax Data Breach Settlement

    foreclosure homes - When Does It Make Sense to Refinance?

    When Does It Make Sense to Refinance?

    no apple

    Get A New iPhone? Heck No!

    poor definition

    Mom Fired For Working At McDonald’s While Kid Plays In Park…Was She Wrong?

    What’s Keeping You From Finding A Job

    U.S. Students Fall Behind: Seemingly Get Dumber By The Minute

    Trending Tags

    • Trump Inauguration
    • United Stated
    • White House
    • Market Stories
    • Election Results
  • Lifestyle
    • All
    • Food
    • Health
    free thanksgiving dinner

    How to Get Your Thanksgiving Meal for Free

    using hand sanitizer, your own

    How to Make Your Own Hand Sanitizer

    Yoga pose

    5 Ways to Practice Yoga on a Budget

    Traveling, new cultures, Start a Saving Challenge, Spend Smart on Food

    3 Simple Ways to Save for A Vacation

    spending money, debt, Take a Roommate, Get a Smaller Home

    Lifestyle Changes To Pay Down Debt Faster

    Trending Tags

    • Golden Globes
    • Game of Thrones
    • MotoGP 2017
    • eSports
    • Fashion Week
  • Investing
  • Make Money
  • Learn Something
  • News
  • Reduce Debt
  • Rental Property
No Result
View All Result
Yes, I Am Cheap
No Result
View All Result

Calculating Your Mortgage Payments with Excel

Sandy Smith by Sandy Smith
August 24, 2019
in Learn Something
22
Calculating Your Mortgage Payments with Excel
Share on FacebookShare on Twitter

This post may contain affiliate links which may compensate us based on your interaction. Please read the disclosures for more information.


Welcome back! Have you checked out the free webinar on how to establish credit for your small business?


I have friend who is getting married in the next two months.  While his fiance is busy planning the big white wedding of her dreams with seemingly no budget, my friend wants to move out of their small condo into a house.  He’s been busy house hunting for a few months and thought that he had found the house of his dreams.  The problem is that even though he makes $80,000 each year from his business, he could not qualify for a loan.  His other problem, like many of us, was that he never took the time to use any tools like a mortgage calculator to find out exactly how much house he could afford. Since I helped him figure this out, I thought that I might as well share this with you as well.

Quite honestly, he can afford the house.  The problem was that his debt-to-income ratio was way too high.  Most of his debt is from open credit lines that he really could afford to pay off from savings including a car note and a personal loan.  He thought that with the rates so cheap, he might as well use the bank’s money and hold on to his own for the time being.  Not so, my friend.

Anyway, anyone shopping for a house these days has to be armed with vital information.  You already know that you need to have a decent credit score, a good down payment, no delinquencies and a stable job history to qualify for a loan.  We’re past that.  Sometimes though, you go through this process and a number gets spat out at the end of the mortgage shopping experience that leaves you breathless.  Instead of having a mortgage broker tell you how much your mortgage will be, why not figure it out yourself before you go shopping?

I really, really like mortgage calculators for this because they take the guess work out of everything and can make allowances for exotic things like adjustable rate mortgages, interest only mortgages, and bi-weekly payments.  But, if you are an Excel freak and would prefer to play with the numbers yourself, have I got a formula for you!

If you have a TRADITIONAL mortgage that isn’t like any of the ones that I mentioned above, you will want to become familiar with this formula:

=PMT(INTEREST RATE%/12,MORTGAGE TERM IN YEARS*12,MORTGAGE AMOUNT)

Of course, you won’t paste that into Excel. You need to replace the words in red with actual numbers.  If you had a 30 year mortgage for $100,000 at 4% interest, the formula would look like this:

=PMT(4%/12,30*12,100000)

Excel should spit out the number $477.42.  Actually, it might show up in red or appear in the negative form because good old Excel considers this to be a debt, which it is.  You didn’t know that Excel would be so much fun, did you?

Anyway, have fun with this!  Try it out and tell me what you think.

Join the newsletter

Subscribe to get our latest content by email.

Success! Now check your email to confirm your subscription.

There was an error submitting your subscription. Please try again.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Related

Previous Post

Putting Yourself In A “Debt Reduction” Mode

Next Post

Four Ways to Upgrade Your Teen’s First Car on a Budget

Sandy Smith

Sandy Smith

I started this blog years ago as a way of keeping myself accountable to my own debt reduction plans. Now I'm using this site to help others get out of debt, and learn about personal finance so that they can live their best lives.

Next Post
Auto Insurance - car upgrade options

Four Ways to Upgrade Your Teen’s First Car on a Budget

Comments 22

  1. PB @ EconomicallyHumble.com says:
    9 years ago

    Thanks for posting this… very useful.

    Reply
  2. Young Professional Finances says:
    9 years ago

    That’s a really convenient formula! It actually got my mortgage payment almost spot on (would have been more accurate if I actually put in the exact number). Too bad it doesn’t also include my tax payment and HOA which makes my monthly payment shoot up. 🙁

    Reply
  3. Young Professional Finances says:
    9 years ago

    That’s a really convenient formula! It actually got my mortgage payment almost spot on (would have been more accurate if I actually put in the exact number). Too bad it doesn’t also include my tax payment and HOA which makes my monthly payment shoot up. 🙁

    Reply
    • Sandy says:
      9 years ago

      I have to work on perfecting that. I thought that this was a neat little trick to how everyone in Excel.

      Reply
  4. Aloysa says:
    9 years ago

    Funny that you wrote about it. I just used this formula to calculate the mortgage rate myself. I always use Excel. The one thing that I trust!

    Reply
  5. Aloysa says:
    9 years ago

    Funny that you wrote about it. I just used this formula to calculate the mortgage rate myself. I always use Excel. The one thing that I trust!

    Reply
  6. Creighton Little says:
    9 years ago

    Hey!
    This is awesome! i love the story and then the transition on how to fix it. I’m no wear close to buying a house yet because i’m only in high-school. But to know how to calculate my mortgage and keep track of how much i’m spending. Thanks a bunch.

    Reply
    • Sandy says:
      9 years ago

      You’re quite welcome. Everyone things that a mortgage is difficult. I try to simplify it with simple mathematics. When that fails, I use a calculator.

      Reply
  7. Creighton Little says:
    9 years ago

    Hey!
    This is awesome! i love the story and then the transition on how to fix it. I’m no wear close to buying a house yet because i’m only in high-school. But to know how to calculate my mortgage and keep track of how much i’m spending. Thanks a bunch.

    Reply
    • Sandy says:
      9 years ago

      You’re quite welcome. Everyone things that a mortgage is difficult. I try to simplify it with simple mathematics. When that fails, I use a calculator.

      Reply
  8. Kari@Small Budget Big Dreams says:
    9 years ago

    Useful tool to have. The bf and I just bought a condo, so we’re definitely going to be very acquainted with this formula (hopefully for less then 30 years).

    Reply
  9. Kari@Small Budget Big Dreams says:
    9 years ago

    Useful tool to have. The bf and I just bought a condo, so we’re definitely going to be very acquainted with this formula (hopefully for less then 30 years).

    Reply
  10. Julie @ Freedom 48 says:
    9 years ago

    I love using the mortgage calculators on my bank’s website. My husband and I own 2 homes and are planning to buy a 3rd – but we need to make sure we can qualify for enough mortgage, in addition to our down payment, to buy the house. Every time we get a raise I hope online and calculate how much more mortgage we can qualify for. It’s motivating!

    Reply
  11. Julie @ Freedom 48 says:
    9 years ago

    I love using the mortgage calculators on my bank’s website. My husband and I own 2 homes and are planning to buy a 3rd – but we need to make sure we can qualify for enough mortgage, in addition to our down payment, to buy the house. Every time we get a raise I hope online and calculate how much more mortgage we can qualify for. It’s motivating!

    Reply
  12. Wayne @ Young Family Finance says:
    9 years ago

    I love using excel for personal finance! Thanks for sharing. It’s important to also add in potential property tax payments for escrow.

    Reply
  13. Wayne @ Young Family Finance says:
    9 years ago

    I love using excel for personal finance! Thanks for sharing. It’s important to also add in potential property tax payments for escrow.

    Reply
  14. Save Money Hound says:
    9 years ago

    Yes, definitely always a good idea to crunch your numbers first before taking out a mortgage. Best to air on the cautious side with the debt that you take out in case of any unforeseen circumstances. It also can be rather shocking to find out how much you have to pay the bank for large mortgages and how long it will take to pay off. It’s a good reality check to crunch your numbers.

    Reply
  15. Save Money Hound says:
    9 years ago

    Yes, definitely always a good idea to crunch your numbers first before taking out a mortgage. Best to air on the cautious side with the debt that you take out in case of any unforeseen circumstances. It also can be rather shocking to find out how much you have to pay the bank for large mortgages and how long it will take to pay off. It’s a good reality check to crunch your numbers.

    Reply
  16. Alan@escapingmydebt says:
    8 years ago

    Hi Sandy,
    This is a good article for those people who are not very familiar with functions/formulas in excel. I knew about the formula but you bring up a good point about being armed for the next time I am out looking to purchase a house.

    Reply
  17. Laura says:
    7 years ago

    The PMT function got my mortgage payment spot on, right to the penny! At least I know where the bank got their numbers from now!

    There’s a pretty complete (and easy to use) Excel spreadsheet which calculates mortgage payments at http://investexcel.net/mortgage-payment-calculator/

    It seems to have a complete set of options for changing payment frequencies, mortgage compounding, etc, and gives a complete payment schedule.

    I entered my mortgage start date, and the other details like the payment frequency, and it precisely predicted the date at which each payment is taken from my bank account.

    Reply
    • Sandy Smith says:
      7 years ago

      That’s a great tool. Thanks for sharing.

      Reply
  18. Michelle says:
    6 years ago

    I knew I should have payed more attention in statistics class for all of these helpful formulas!

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stay Connected

  • 5.5k Fans
  • 10.3k Followers
  • 4.7k Followers
ADVERTISEMENT
  • Trending
  • Comments
  • Latest
The Government Gives Free Appliances? Yup!

The Government Gives Free Appliances? Yup!

August 24, 2019
Cost of Living in the 1950’s as Compared to Today in 2013

Cost of Living in the 1950’s as Compared to Today in 2013

August 24, 2019
Tax Refund - Don't Have Kids If You Can't Afford Them

Don’t Have Kids If You Can’t Afford Them

August 24, 2019
side hustle during quarantine

Top Side Hustles to Boost Your Income During Quarantine and After

October 5, 2020
Tesla Stock Price

Why Tesla Stock Performed So Well In 2020

January 13, 2021
stimulus

Why You Might Not Get A Stimulus Payment

January 6, 2021
emergency fund saving money

Gamify Saving Your Emergency Fund in 2021

January 6, 2021
how to get a raise

The Best Ways to Get A Raise (Even In A Pandemic)

December 8, 2020

Recent News

Tesla Stock Price

Why Tesla Stock Performed So Well In 2020

January 13, 2021
stimulus

Why You Might Not Get A Stimulus Payment

January 6, 2021
emergency fund saving money

Gamify Saving Your Emergency Fund in 2021

January 6, 2021
how to get a raise

The Best Ways to Get A Raise (Even In A Pandemic)

December 8, 2020

Helping you live your best financial life through personal finance news and education

Follow Us Everywhere

Browse by Category

  • About Me
  • Auto
  • Credit
  • Credit Cards
  • Finance 101
  • Food
  • Get Out of Debt Course
  • Health
  • Investing
  • Learn Something
  • Lifestyle
  • Make Money
  • News
  • Off The Reservation
  • Partner Posts
  • Politics
  • Rants
  • Reader Advice
  • Recipes
  • Reduce Debt
  • Rental Property
  • Save Money
  • Scams
  • Special Offers
  • Spend Money
  • Sponsored Posts
  • Video Pick
  • Weekly Roundup
  • Work

Recent News

Tesla Stock Price

Why Tesla Stock Performed So Well In 2020

January 13, 2021
stimulus

Why You Might Not Get A Stimulus Payment

January 6, 2021
  • About
  • Advertise
  • Privacy & Policy
  • Contact

© 2021 JNews - Premium WordPress news & magazine theme by Jegtheme.

No Result
View All Result
  • About Me
  • Courses
  • Contact
    • Ads
  • Press & Media
  • Free eBook
  • Free Facebook Group
  • Disclosures
    • Privacy

© 2021 JNews - Premium WordPress news & magazine theme by Jegtheme.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are as essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled

Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.

Non-necessary

Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.