A few weeks ago, we had presented a good way to build a mortgage calculator in excel. We received a lot of positive feedback and suggestions which we always appreciate. One of those was that we could have used a dropdown menu instead of having users type the payment frequency. I agree 100%. Why?

-Dropdown menu would diminish the time required to fill out the form
-More Importantly: A dropdown menu would help us avoid typos by those filling out the form.

There is no good argument to not use a dropdown menu except for not knowing how to do it. I decided to write a quick post to go over how to add a dropdown menu in excel. It’s a lot easier than you could imagine. If you remember, this was the form that was currently in the spreadsheet:

What I would like to do is simply avoid having to type “bi-weekly”. How? There are a few different ways to do it. The first question you should ask yourself is:

-Do I want to define the possible answers?

The answer is yes in this case (it usually is) as I want to only 4 give 4 options for the menu. How do I proceed? First off, anywhere in the spreadsheet, I should write down the 4 possible choices, in the cells next to each other. Here is what I will do:

You could put this on another sheet, hide those rows or change the color, it’s not important for the user to see them. Then, I will create the dropdown menu. In excel 2003, I will select the cell where I want it and select “data/validation”:

Then, in the “settings” tab, simply select “source” and choose the possibilities. In my case, I select the 4 cells:

Then I click ok and you can see the result:

That’s it? Looks easy? It actually is. You can download our new improved spreadsheet with the dropdown menu here

Popularity: 1% [?]

Dear Readers,

We have been fortunate to get many different emails about your excel questions and issues and would love to get even more feedback from you.

What type of information are you looking to get?

Which functions do you have trouble using?

Do you also use macros and if so, do you need any assistance doing so?

What would you like to do through excel but have trouble doing?

Are there tasks that you perform very often that should/could be automated but that you do not know how to do?

Please either write a comment or email at thefinancialblogger (at) gmail (dot) com.

You can also give us the questions here!

Popularity: 1% [?]

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

Popularity: 1% [?]

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

Popularity: 1% [?]

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

Popularity: 2% [?]