Pro tip (for nerds): Stress testing your mortgage budget

Pro tip (for nerds): Stress testing your mortgage budget

Brian Reed

June 22, 2021

Pro tip (for nerds): Stress testing your mortgage budget
Reading Time:

What people don’t think about

Buying a home is the biggest financial decision most people make in their life. But deciding how much home you can afford can be stressful and complicated. Standard ratios like DTI often oversimplify your unique financial situation. They ignore month-to-month variation in your expenses and the impact a mortgage payment can have on your other important savings goals like education, vacation, and retirement. If you don’t think about these trade-offs, you risk committing to a mortgage you can’t afford as easily as you thought.  

Be better than your budget

A simple budget is great. Unfortunately, a budget doesn’t tell you everything you might want to consider before you commit to a mortgage. For instance, a budget doesn’t tell you if it's realistic to achieve. A budget also doesn’t tell you how much of a savings buffer would be appropriate based on your past income and spending behavior. Individuals rarely consider these questions, but there is an approach that can help, if you’re willing to embrace your inner Excel nerd... 

Supercharge your budget with a forecast

You can convert your budget to a forecast that simulates your income and spending based on your past behavior. All you need is an Excel spreadsheet and some Excel formulas. Included in this post, we'll give you a finished example of a forecast in Excel you can download and edit for free to create your own personalized forecast. The remainder of this article walks you through the example step-by-step. 

Download free excel budget forecast example →
Download free excel budget forecast

Benefits of forecasting

A simple forecast of your income and expenses can help you make a better home purchase decision. A forecast can...

  • Provide a more accurate estimate of the average monthly savings you can expect to realize after taking on your expected mortgage payment
  • Help you decide how realistic a target budget would be to achieve based on your past income and spending habits
  • Stress test your budget and emergency savings by simulating 12-month scenarios and calculating the cumulative impact on your savings 
  • Assist in determining whether you can achieve your mortgage budget goals by reducing spending or increasing income 

Example: Building a forecast

Part 1: Define the goals

Pretend we’re building this forecast for a moderately risk averse person, named Andy. Andy is married with no kids. She feels comfortable buying a home as long as she can reasonably save $1000 each month for her other savings goals (like education and family vacation). To restate her goal for our forecast, we would say: Andy wants a monthly mortgage that allows her to save at least $1000 per month with at least 50% certainty. If in our forecast, Andy can’t save $1000 per month in at least 50% of months in our simulation, she’s not comfortable with the mortgage.

Note: Goals should be set based on your individual financial situation and risk tolerance.  

Part 2: Lay the groundwork

Andy collects all her income and spending transactions for the last 12 month months in a single Excel spreadsheet. This is easy to do because she can export her transaction from her bank account and budgeting app. The exported data includes the transaction amount, description, and date. She then groups her transactions into categories and tags each category as 1) Fixed or Variable, and 2) Rigid or Flexible. 

Part 3: Calculate historical income and spending

Once she has her categories set, she adds up her income and spending in each category in each month for the last 12 months. The SUMIFS function in Excel using the date and category columns makes this easy. Once finished, she has a table showing her total income and spending in each category in each month for the last 12 months. Finally, she calculates some basic summary statistics for each category. The AVERAGE, MEDIAN, STDEV, MIN, and MAX functions in Excel make this quick to do. 

Download free excel budget forecast example →
Download free excel budget forecast

Part 4: The nerdy part

Now Andy creates a column in her table for Month “X,” her forecasted month. Here she predicts her future monthly savings based on her past income and spending and her month-to-month variation in income and spending. The good news is you don't need to calculate this from scratch! You can reuse the Excel formulas Andy created to generate her forecast by downloading the example Excel file. You can also reference Youtube videos here and here if you want to go deeper.

Now Andy has a completed one-month forecast of her income and spending! But she’s not finished. One more step will dramatically increase the power of her forecast...

Part 5: Run simulations

Andy uses a simple macro to get Excel to automatically simulate as many forecasted months as she would like. If macros seem complicated, you can use Andy’s example so you don’t have to create the Macro for yourself. Because she wants to be confident in her forecast, Andy chooses to simulate 2000 months! She then calculates the average savings from each simulated month and uncovers some powerful insights! Let’s see what she learned...

Part 6: Insights

In this example, Andy is considering a $2000 monthly mortgage payment. If Andy simply averages her end-of-month savings for the past 12 months, she might think things look ok. On average, she saves $247 a month. It doesn’t meet her goal, but maybe it could still work?

Luckily, Andy built a forecast and she simulates 2000 months of income and spending. Her forecast tells her she would actually be short about $18 a month. She won’t be able to afford this mortgage unless she changes something about her income and spending. 

Out of the 2000 simulated months, Andy counts the number of months in which she saves at least $500 and at least $1000. Turns out, she is able to save $1000 in 25% of the simulations, and $500 in 45%. That doesn’t meet her goal of saving $1000 with at least 50% certainty. 

Next, Andy wonders how much of a savings buffer she needs if she moves forward with this purchase. So she sets up a chart that puts together six sets of 12-month periods from the 2000 simulations. The chart shows her what could happen to her savings balance if she committed to a $2000 mortgage and maintained her income and spending behavior. In two of the six scenarios, Andy’s savings balance declines significantly over a 12-month period. To be safe, she might need $10,000 in an emergency savings buffer to afford a $2000 monthly mortgage payment.

Andy is glad she built this forecast! She decides she can’t afford a $2000 mortgage unless she increases her income or reduces her spending. But she can easily test these scenarios using the same forecast by inputting new values for income and spending into Excel. Soon Andy will be able to land on a mortgage budget she feels comfortable with, and she moves forward a little more confident in her home buying decision! 

Note: This forecast is meant to be a helpful supplement to your other budgeting and personal finance analysis. While helpful, it is not sufficient on its own to tell you what your mortgage budget should be. 

Download free excel budget forecast example

Thank you! Your your download is ready. Please click below!
Download Now
Oops! Something went wrong while submitting the form.

Other blogs you might like...

No items found.