How much to borrow for a 401(k) loan

[Dan]

Hi,

I'm finding this challenge, well, challenging...I want to borrow funds from
my 401(k), and the basic parameters are:

1) interest rate is 7%

2) loan period is 5 years

3) payments are monthly

I have the option to pay it off early, and I plan on doing so in 3 years.
The catch is that I want to borrow an amount that covers all of my payments
during the 3 years, so that I have no out-of-pocket payments during that
time.

For example, I need $10,000 to do some house remodelling. So, I actually
need to borrow something like $13,000...$10,000 for the house, and $3,000 to
cover three years' worth of payments on a 5-year $13,000 loan.

Any ideas on a formula for this (so that I can play around with the loan
amount)?

Thanks!

Dan
Colorado

 

[Bernie Deitrick]

Dan,

You would need to borrow $34,824.17.

To find this, you need to use Solver.
In cell B2, enter the formula

=PMT(7%/12,60,-A2)*36
(this is the total of payments for 3 years (36 months) for a 5 year loan at 7%)

Then in cell C2, enter the formula
=A2-B2

and use solver to set C2 to a value of 10,000 by changing cell A2.

BUT: Note that you are falling prey to a very common misperception - that you are "paying interest
to yourself" when you take out a loan from your 401K, and that it then really doesn't cost you
anything. Do all the calcs, and you will see that your plan will cost you quite a bit more to do
this than to take out a personal loan or a home equity loan.

The Bottom Lline: Take out the minimum amount from the cheapest cost source (based on interest rate)
available. If that is your 401K, then use that, but do not take out the extra amount to cover the
payments.

Let's say that you pay off your proposed 401K loan in three years by making larger payments. To pay
off $34,824.17 in three years requires

=PMT(7%/12,36,-34824.17)*36

OR $38,709.69, for a total interest cost of $3,885.52 ($38,709.69 - $34,824.17)

If you took out a simple loan of 10,000 for three years at 7%

=PMT(7%/12,36,-10000)

Your payments would be $308.77 per month, for a total payback of $11,115.75, and a total interest
cost of $1,115.75.

So your scheme would end up costing you an extra $2,769.77.

HTH,
Bernie
MS Excel MVP