Simplifying Loan Payments with Microsoft Excel

All of us have been tempted every now and then to succumb to the allure of that fancy new LED HD TV, or perhaps the latest and greatest version of the iPhone. Or maybe you’ve had your eye on that fancy sports car and can’t wait to own one.

Well, except for the lucky few who are able to fall back on their millions, these kind of temptations imply some kind of a loan.

And here’s the thing – there’s an entire industry out there that tries it’s best to confuse you about exactly how much is due from you, month on month. Some of them will tell you that no payments are due for up to six months after you buy the product, while others will talk about zero down payment. Still others will lure you with ‘teaser rates’… you get my drift, right?

Time for the good news – Microsoft Excel steps up to the plate, and simplifies matters for you.

Here’s how:

Let’s say you’ve decided to go and buy yourself the simply the best vacation around – a month week stay at the most expensive resort in Hawaii, first class tickets to and fro – the works. The only problem is, your travel agent estimates the costs to come out to around 100,000 USD. That’s a cool hundred grand, right there.

So if you were to take a loan, exactly how much would the monthly payment be?

Well, if you were to take a five year loan at 10%, here’s what the basic information looks like.

Excel

So, at the end of the day, what’s the monthly damage to your wallet? Ignore all of what the agent has to say, and rush to your laptop. Excel has the answer!

Excel

The syntax is simple:

  1. PMT(rate, nper, pv, [fv], [type])

  2. rate: is nothing but the interest rate payable, which in our case is 10%

  3. nper: the number of periods of payments (60 months as far as we’re concerned)

  4. pv: the principal value (100 grand in our little tale)

  5. fv: the future value, which you can safely ignore here

  6. type: if you ignore this, Excel assumes that payments are due at the end of the period. If you put in the value 1 (one), Excel assumes that payments are due at the beginning of the period.

Well, simply enter the values as shown below, and ta-da! You’re good to go!

Excel

Once you hit enter, Excel tells you exactly how much you will owe on this loan for the terms mentioned. Anything more, and you got a problem on your hands!

Data

And that’s that.

Oh and hey – do let us know how that vacation worked out.

Functions, Formulas, Tips and tricksExcel EverestExcel tutorial