Mortgage, Down Payment, and Affordability Calculator (2024)

1

Mortgage, Down Payment, and Affordability Calculator

2

3

Use this spreadsheet to calculate if you can afford a home. Standard 20% down, and FHA mortgages are analyzed. It will include the sale of your current house as well if you have one to sell.

4

If you want to figure out if you should rent or buy a home, click here to look at my other spreadsheet

5

Instructions:

6

1. Click File and then Make A Copy

7

2. Enter your own information in the yellow cells

8

3. Look at the information at the bottom to see if/when you can afford your house!

9

10

PLEASE CLICK FILE THEN MAKE A COPY TO ENTER YOUR INFORMATION!

11

DO NOT REQUEST EDIT ACCESS TO THIS SPREADSHEET

12

13

Once you've made a copy of this with File and Make A Copy, edit values in the yellow cells, Do NOT modify values in any other cell, these will be computed once you enter values in the yellow cells.

14

15

First Salary:$75,000(your yearly income before taxes)

16

Second Salary:$50,000(If there is someone else (like a spouse) who is sharing in payment for the mortgage, enter their yearly income before taxes)

17

Selling another house first?(If you currently own a house and are selling it, check this)

18

Current Mortgage amount remaining:$200,000(amount left on your current mortgage if you have one and are selling the house)

19

Home sale price (est.):$300,000(how much you think you can sell your current house for, if you are planning to sell it)

20

Realtor commission percent:6.00%(percent that the realtors will take of the sale price, usually 6)

21

Closing cost percent (est.):3.00%(estimate of the percent of the sale price that closing costs will be)

22

New home purchase price:$450,000(How much the new house will cost)

23

Property tax rate:1.04%(yearly percent of the new house paid for property taxes, varies by county/city)

24

HOA/Condo Fees (monthly):$90

25

Home Insurance (monthly):$40(estimated monthly cost of home insurance on the new house)

26

Monthly Property Tax:$390(how much property taxes will be per month on the new house)

27

80% Mortgage Rate (percent):5.500%(mortgage rate for the standard mortgage)

28

Mortgage Points1.580(points (percent of the loan) that you have to pay upfront to open the mortgage. This is a fee, and is not part of the down payment)

29

FHA Mortgage Rate (percent):5.500%(mortgage rate for an FHA mortgage)

30

Current Accessible Savings:$25,000(how much you currently have in savings that you can use for the down payment before selling your house. Don't count retirement accounts, college savings, etc.)

31

Savings required to keep:$10,000(how much you need to keep in these savings after purchasing the home. You should still leave aside some money and not use all of your savings for the home purchase)

32

Profit After Current Home Sale:$73,000(how much money you will have after your home sale, if this is negative you're under water)

33

How much are you saving per month:$500(estimate of how much you are currently saving per month in accessible savings. Retirement, college funds, etc. don't count here)

34

35

Adjust the percent below to use a different down payment percent for the FHA mortgage. The minimum is 3.5%

36

FHA Down Payment %:5%

37

Standard 20% Down Mortgage:FHA Mortgage:

38

Down Payment:$90,000Down Payment:$22,500

39

Mortgage Amount:$360,0001.5% Upfront MIP:$6,413(this is an upfront mortgage insurance cost required for FHA mortgages)

40

Point Cost:$5,688Mortgage Amount:$427,500

41

Savings amount left with:$2,312Savings Amount Left With:$69,088

42

(how much you'll have in savings after selling your house and make the down payment on the new house. If this is less than the amount of savings that you need to keep, you can't make the move yet)

43

Monthly Principal:$394Monthly Principal:$468

44

Monthly Interest:$1,650Monthly Interest:$1,959

45

Monthly PMI (0.85%):$303(this is required mortgage insurance payments every month until you have paid for 22% of the home value)

46

Total New Monthly Payment:$2,564Total New Monthly Payment:$3,250

47

(mortgage, property taxes, HOA fees, insurance)(mortgage, property taxes, HOA fees, insurance, PMI)

48

Payment as % of Income:24.61%Payment as % of income:31.20%

49

(this should be less than 28%, if it's more you should probably look for a cheaper new house)

50

51

Months saving needed for down payment:15Months savings needed for down payment:None!

52

(How many more months you need to save to be able to make the down payment after the purchase, without going below the amount of savings that you need to keep. If this says None!, you have enough in savings right now to make the down payment after the sale)

53

54

Total paid after 2 years:$151,537Total paid after 2 years:$106,915

55

Total paid after 5 years:$243,842Total paid after 5 years:$223,919

56

Total paid after 10 years:$397,685Total paid after 10 years:$415,292

57

Total paid after 20 years:$705,370Total paid after 20 years:$768,968

58

Total paid after 30 years:$1,013,055Total paid after 30 years:$1,122,644

59

(assuming you make the minimum monthly payments and will get to 22% paid off after 9 years, when you can stop paying PMI)

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78