You would think that finding the interest rate being paid on a credit card or another type of loan would be easy right? I mean, the statements generally do include the interest rate that you are being charged. Of course, there are many different ways to present an interest rate and while the ideal and most representative way would be to always include the effective rate, it is not always in the advantage of the lender to present it that way. Why? Because that rate is generally higher and could encourage you to look for a better deal. The companies figure that consumers will not see the difference and in almost all cases they are correct.

Here is a simple way to calculate your charged interest rate, in this example we will use a simple loan but this could be adapted to a number of different situations. You will need a number of different information in order to make the calculation.

End of last period: August 25th
End of this period: September 23rd
Outstanding Loan at the last period: $18,575.00
Interest Rate Charged: $111

Let’s review the formula to calculate interest:

Interest = Loan Amount * Interest Rate * (nb days/365)

In this case, we are missing the interest rate so we will shuffle around the formula:

Interest Rate = Interest / (Loan Amount/ (nb days/365)

In our example that would be:

Interest Rate = $111/18575/(29/365) = 7,52%

See a chart here or you can even download the spreadsheet here

One of the more common questions that we have received lately is how to build mortgage calculators in excel. It’s actually fairly easy to do but it depends on how much complexity you add of course. Using the “PMT” excel function will help you but what if you want to test the mortgage payment amount depending on the frequency of your payments, on the amount you will borrow, on the rate and of course on the length of your mortgage? it’s all possible. In this spreadsheet (link below to download it for free), I used nested if statements in order to determine the number of periods based off of the frequency of payments and the rate to use. It is fairly easy to go through, here is an image:

Download our free excel mortgage payment calculator spreadsheet here

All numbers in the lighter color are those that you must enter:

-House Price
-Down payment
-Mortgage time length
-Fixed Rate
-Payment frequency (since we used “if” conditions, the frequency must be entered as described on the right)

Depending on the frequency, I will determine the number of periods. Why? Because if you pay every month, you will not be charged interest on the capital that you paid back the following week. That is why it makes such a big difference to pay a mortgage bi-weekly compared with monthly (that and the fact that you actually have more payments because of the 30 or 31 days per month!).

Download our free excel mortgage payment calculator spreadsheet here

One of the recent comments that we received on our “Nested If Statements in Excel with “And” or “Not”, was an interesting example. Here is the question that we received:

I would like to know if it is possible to write a formula to determine “pass” or “fail” given the following criteria:
The scores are given as follows in excel :
example : (subjects are named A to G)
The subjects are typed in left to right in one row, not in columns.

criteria :Subject A must be >= 40, from B to G there must two other subjects >=40 and three additional >=30 – for a student to pass.

In theory, it would be possible to integrate all of that into one single formula. There are however many problems. It would be very difficult and time consuming to build and changing it would be a nightmare. Why not break down the problem down? Here is how I did it:

-Determined if the score for every subject was over or equal to 30
-Determined if the score for every subject was over or equal to 40

Once that was done, I had every information to determine the acceptance or not of each of the three conditions. It looks complex but it’s actually very easy to do and was very quick. Here is the look of my spreadsheet, which you can also download later on:

There are multiple advantages to using this more detailed method in such a case:

1-Easier to understand, we can easily see why failed students did not pass.
2-Easy to modify and adjust over time

You Can Download The SpreadSheet Here

We’re thrilled to say that we’ve been getting an increasing amount of reader questions with their own excel challenges or problems. It’s always interesting because no matter what the subject or the exact problem is, we can usually learn something that can be applied at some point in the future. In this case, we received an email from a reader that is attempting to save how much savings she is making using simple coupons. The only twist? All coupons of $0.50 or less are doubled by the store. Simple enough? Our reader was attempting to either use the “nested IF statements or an “else if”. She also sent me a sample file which I’ve modified slightly and will let you see at the end of this post.

As in almost any other excel problem, the main challenge is usually to “see the problem in the simplest way”. It’s much easier said than done of course. In this case, what is the situation exactly?

-Buyer buys a given number of items
-For each item there may be a coupon
-That coupon should be applied but any coupon worth less than $0.50 should be doubled in value.

Personally, I think we can easily resolve this problem with a simple “if” condition without adding any “or else” or “nested if” conditions. It’s always about finding the easiest way which not only saves us time but also makes it much easier to modify and improve the fills.

In this case, here is how I see it:

Personally, I would simply calculate the total as follows:

Total = If coupon > $0.50, Quantity x Price – Coupon Value, Quantity x price – 2 * Coupon Value

Why? There are really only 2 cases here. A coupon worth less than $0.50 or not.

cell E2 = IF(B2>0.5,C2*D2-B2,C2*D2-2*B2)

That’s it!! You can download our spreadsheet here!

If ever you have questions relating to this or any other excel issue, please either ask on this post or contact us!

Instead of writing another long Excel function tutorial, I wanted to dedicate a quick post on how to make your Excel models look 100x better. No matter what version of Excel you’re using, there are three easy things you can do to make your tables and models easier on the eyes, easier to use, and more professional. I’ll show you the transformation alongside an example.

First, let’s suppose this simple table is what we originally start with:

Read the rest of this entry »