Big Audacious Debt Payoff Plan Part 1

I realize with this being a blog about Saving and Swimming that you may think BADPP is referring to what we are all NOT supposed to do in the pool. However, we have much more important financial things to discuss than not peeing in the pool, which you should already not do.

The Idea

I recently began listening the book Get Smart! How to Think and Act Like the Most Successful and Highest-Paid People in Every Field by Bryan Tracy. The content is pretty good, but the author narration could be better. He mentions that 3% of people write their goals down and carry them out, but 97% do not. If you write your goals, devise a plan to achieve them, and track your progress to completion, you will become part of this rare 3% of people in the world. That sounds pretty good to me.

I began thinking about our financial goals. We manage our finances well, but we have a disappointing amount of debt. I decided that I would come up with a BADPP. I made up this acronym which stands for Big Audacious Debt Payoff Plan. In Part 1, we are going to set a goal to pay off our Home Equity Line of Credit (HELOC). This loan started with an introductory rate of 3.5%, but with each interest rate hike by the Federal Reserve Bank, the interest rate on the loan increased. It is now at 6.25% which adds $125 per month to our balance. This is $125 per month we could certainly put to better use.

The Goal

Our goal is to pay off the remaining $24,000 balance in our HELOC during 2019. There are 13 months until the end of 2019. We pay about $625 per month, but we need to pay at least $1,914.16 per month to pay it off by the end of next year. Here is the monthly payoff schedule I created in Excel.

Excel Loan Payment

If you want to know how I calculated the monthly payment amount with interest, keep reading. I include a lesson at the end of this post to teach you an easy way to do this in Microsoft Excel.

This seems a daunting amount above our monthly payment especially with all the other bills we have to pay. However, I believe we can find the money to make this much larger monthly payment within our current savings and reallocate it to pay off this debt.

The Plan

In recent months, I paid $500 plus the interest charged for the current month on the HELOC. I did this to decrease the balance by $500 per month. This is a good strategy for getting ahead on a debt, but not good enough to pay off this debt in 13 months.

I sat down with my favorite fountain pen, a pad of paper, and wrote all the ways that we could pay this off in the next year. We have a Chase Freedom credit card at 0% APR. This card has a $1,800 balance with a monthly payment of $300. We could pay it off, but we will have it paid in six months and are not paying any interest. I decided to keep it as is and kept looking.

We recently paid off a credit card we used as a 0% loan to buy some home furnishings. We paid $250 per month on this card for over a year. In October we finally paid it off. Snowballing this amount into our current monthly HELOC payment gets us to $875 per month. That’s good, but is still $1,039.16 short of our required monthly payment.

Our current emergency savings is $9,700. We devote $700 of our monthly income toward emergency savings. This is a significant increase from our 2017 balance of $4,500. Redirecting the $700 toward paying on our HELOC increases our payment to $1,625 per month. This leaves $289.16 per month.

We currently deposit $200 per month into a 529 account for Baby SoS. I started the account when she was born. The first year I put $100 per month into the account. When she turned 1, I increased the amount to $150 per month. When she turned 2, I increased the amount to $200 per month. The plan was to increase this again when she turns 3 in January to $250 per month. If I pause the monthly deposits into this account, we can add $250 to our monthly payment. We are down to $39.16 per month.

We are cleaning up our diet and to that end we decided to stop purchasing alcohol. I told Dr. SoS we should stop buying any alcohol until we have the HELOC paid off. We typically buy a bottle of Ketel One in the 1.75L bottle at Costco on no less than a monthly basis. The price fluctuates, but after tax, this is usually about $39.00. Look at that, with just a little thinking and planning we only need a few more cents to make up the difference. I don’t think this will be a problem.

What Else…

We auto-pay our Chase Freedom card on the 19th of each month. Our last payment is April 19th. Once paid, we can apply this $300 toward our balance to pay it off even faster.

Last year, we received a tax refund of $5,000. Our accountant files our personal and business taxes. We will not know what our refund might be until next year. We plan to use the entire amount to pay on the HELOC. This could considerably decrease the payoff time.

I receive my annual cost of living raise in March. This is a guaranteed amount each year. Looking at the increase from last year, this is $130 more per month. We will apply this entire amount toward the HELOC, as well.

Dr. SoS began meal planning once again and this would definitely generate even more money we could pay toward our loan.

Conclusion

Many people get so overwhelmed by large amounts of debt that they do not figure out what is possible. We could continue paying the $625 per month and have the loan paid off by June 2022 and pay over $2,846 in added interest or we could stick with our new plan and cut the interest to $884 and perhaps even much less.

Write your debt payment goals down by hand. Scheme this way and that and look for ways to increase the payments on your debt. Once you get one debt paid off, snowball that amount into the next debt. Once you have all your debts paid off, snowball that directly into maxing out your retirement and HSA accounts. These are the basic steps toward financially independence.

This is just the first part of our BADPP. Stay tuned as we do this and much more!

Not only do we know that WE can do it, but we know YOU can do it, too!

Mr. SoS

Excel Training with Mr. SoS

In a past life, I was a Microsoft Excel expert. I have an occasional side hustle using my Excel mastery, but really only use it now for my net worth and options trading calculations. The Save or Swim blog is for saving and swimming, but there are probably plenty of readers who could use some Excel help or would like to learn some nifty Excel shortcuts. This is for you.

The Problem

We want to pay off a debt amount and need to calculate how much we should pay each month to have it paid off in a specific amount of time including compounded interest. The amount may differ due to how your bank calculates the interest and when you make your payments, but it will be close.

The Setup

I am using Microsoft Office Professional Plus 2016, but the method will be very similar for older and future versions. If your version does not look exactly the same, you should still be able to do the calculations. If you have any issues, contact me and I will help you.

Open Excel. If you do not see a blank workbook, press Ctrl+N to open a new workbook. In row 1 in the cells across the top enter Month in column A, Balance in column B, Interest in column C, and Payment in column D.

Now for row 2. Under Month in column A, enter the first month date, 12/01/2018. In column B under Balance enter your debt balance. Our balance is 24000. The Interest in column C has our first Excel formula. Our interest rate is 6.5%. The easiest way to enter this formula is to enter the formula =B2*6.25%/12. The 6.5% is an annual rate and to simplify things we will divide the amount by 12 for the months of the year. For the Payment in column D, I entered $625 since this is the amount we are currently paying. This is what it should look like.

Excel Loan Payment 01

We need to increment the months in our spreadsheet. There are many ways to increase the month, but I am going to show you an easy formula to increment the month by 1. In cell A3, which would be column A row 3, enter =DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)).

Next, we will calculate the updated beginning month balance. This is pretty easy. It is the previous balance plus the monthly interest minus the monthly payment. In cell B3 enter =B2+C2-D2.

The interest is the same formula that we entered in cell C2. The easiest way to copy this is to click in cell C3 and then press Ctrl+D to copy the formula down from C2 to C3. You could always use the Ctrl+C to copy the original formula and Ctrl+V to paste, but I like to save the trouble with the single Ctrl+D shortcut.

Lastly, we want to use the same payment for the month so in D3 enter =D2. Now your spreadsheet should look like the following.

Excel Loan Payment 02

If you want to double-check your formulas with mine, press Ctrl+~ (the tilde is to the left of the 1 on the top row). This will show all the formulas in your spreadsheet. You may have to make column A wider to view the entire formula. Compare yours to what I show below and then press Ctrl+~ once again to switch the view back to your working spreadsheet.

Excel Loan Payment 03

Next, we need to copy our formulas down. Click cell A3 and drag your mouse over to cell D3. The result should look like the following.

Excel Loan Payment 04

There are many ways to copy the formulas, but one easy way is to use the cell anchor. In the bottom right corner of the far-right cell in D3, in the image above, you will see a little square. Click this square and drag down to cell D14. The copies all the formulas down as shown in the following image.

Excel Loan Payment 05

In cell A15, type Total. Click cell B15 and then press Ctrl+D to copy the formula down. Click in cell C15 and press Alt+=. This automatically enters the formula =SUM(C2:C14) for you. Press the Enter key to enter the formula. The image below shows just before you press Enter.

Excel Loan Payment 06

Before we arrive at our answer, we will format our spreadsheet. Click on the column A to highlight the entire column. Next press the Ctrl+1 (this is the 1 on the top row of your keyboard) to open the Format Cells dialog box. In the Category list box click Custom at the bottom of the list. This provides you with unlimited formatting capabilities. We want to see the year and then the month. In the Type: box enter yyyy-mmm. This changes the format to show the full four-digit year and then the first three letters for the month. Feel free to change this however you would like.

Excel Loan Payment 07

While we are in the Format Cells dialog box, click the Alignment tab and change the Text alignment Horizontal from General to Center. Click the OK button.

Next click the header for column B and drag over to column D. In the Home tab under the Number section (the fourth section over) click the $. This formats Balance, Interest, and Payment columns to the Accounting format with 2 decimal places. Your updated sheet should look like the following.

Excel Loan Payment 08

Now is the part that you have read through all of this setup to find. We are going to use the Goal Seek Forecast function within Excel What-If Analysis to calculate our monthly payment. This sounds difficult, but it is very handy and is a snap to do. Before we start, click cell B15, which now holds the value $17,292.94. Next, click the Data tab. Way over to the right in the Forecast section of the toolbar you will see What-If Analysis. Click that button. A drop-down menu will appear. Select Goal Seek… from the menu.

This should open the Goal Seek dialog box. It should already have the Set cell: B15 entered. In the To Value: enter 0, and in the By changing cell: enter D2. It should look like the following.

Excel Loan Payment 09

Once you click the OK button Excel will magically find the result that will calculate a monthly payment with interest that allows you to arrive at $0.00 after your final 13th payment. The spreadsheet also automatically calculated that we would pay $884.09 in interest. Here is the result, which you saw when you started reading this post and it provided our monthly payment required of $1,914.16.

Excel Loan Payment

If you read this far, hopefully you learned something new today. If so, let me know in the comments. Please also let me know if you used this method to calculate your next Big Audacious Debt Payoff Plan! Oh, and do not forget that Pee in Pool is BADPP!

2 comments

  1. All good excel tips! Don’t know what I’d do without What-if analysis!

    Maybe one suggestion: instead of changing the payment amount in col D, you could two more columns; col E for the extra you’re paying on top of scheduled payment and col F the total you’ll pay that month. Might give a better representation to people who aren’t familiar with amortizing loans.

    You could also show how the regular payment is calculated with the excel formula.

    1. Thanks for the comments NWA-non. The minimum monthly payment is $215 per month. In recent months, I paid no less than $500 plus outstanding interest. I agree with your suggestion adding a couple more columns to track minimum payment and the additional payment. Then you could use the PMT function to figure out what your new payment would need to be to have the loan paid in the remaining number of months. In fact, if you just enter =PMT(rate,NPER,PV) you can calculate the monthly payment needed to pay off a loan with compound interest. Here’s the formula to calculate my loan payment =PMT(6.25%/12,13,24000) to have it paid by December 2019.

Leave a Reply

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

 characters available