Algebra Again?!?

I have a loan that I totally forgot about.  The lender, although very understanding, just called and asked when we’d be making the next payment.  Unfortunately, I can’t locate the original paperwork, if we ever had any.  All I have is record of one payment made for the interest accrued to that point.  How do I figure out what the interest rate on the loan is, then book and track (create amortization table) it so this doesn’t happen to us again?

Once again, my high school Algebra teacher is yelling in my ear.

After a little scribbling on paper and playing with Excel, I come up with the following:

Known variables:

  1. Amount of Loan (50k)
  2. Months between loan and first (interest only) payment (5)
  3. Amount of interest only payment ($1,050.38)
  4. Total amount due after 5 months {principle + accrued interest} ($51,050.38)

And here’s the formula that pulls it all together:

50k (1+r)^5=$51,050.38

If you remember, we have to solve for r (the interest rate), right?

Sorry, for those for whom this is insulting, but I’m hopeful there will be people reading this that can gain from the step by step approach and the assumption that every detail is important:

So, solving for r:

Step one: Divide both sides by 50k
(1+r)^5 = ($51,050.38/$50,000)
Note: the ^ is Excel’s equivalent of ‘raised to the power of
(1+r)^5 = (1.0210077)

Step two: take the 5th root of both sides; if you remember, the nth root of a number is the same as raising the number to 1/n.  So, the next step:
(1+r)=( 1.0210077)^(1/5)

Strep three: now simply subtract the remaining 1 from both sides:
r=( 1.0210077)^(1/5)-1

And once you have this, you have everything you need to build an amortization table in Excel. I’ll go over a clean and easy way to build one to your liking, but Microsoft also has some very nice templates.


About bbluford
I am an executive finance professional with a love for process and application development (MS Access, Excel, Quickbooks), mostly as it relates to Accounting and Business Functions. I also love to write and share ideas with other people in this world. I'm an admitted Gym Rat who works out excessively. The best summation of me is that I love to teach and to learn.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: