Calculating a loan repayment with the PMT function

Here's an Excel function we could all make use of.  Calculating the repayment on a loan.

In order for this calculation to work, you require 3 items:  the amount being borrowed; the interest rate that will be charged; and the term over which the loan will be re-payed.

First layout your spreadsheet with the required information, and click into the cell that you would like the answer (i.e. the monthly installment) to appear.  Now switch to the Formulas tab on the ribbon, and from the Financial list scroll down to find the PMT calculation.

The Function Arguments window will appear with 3 required fields (for the information that you have ready) and 2 further optional fields that I'll explain a little further down.  A clue to indicate which fields are required vs which are optional, is that the required fields have a bold title.

Place your cursor into the first required field, Rate, and click once on the cell that contains your interest rate value.  Typically the interest rate that you are quoted is the APR (Annual Percentage Rate) and as such the amount is what you will be charged per year.  Because we are attempting to calculate the monthly installment, we need to give Excel the monthly interest rate.  You are able to do so by dividing this APR value by 12.

Now place your cursor into the second required field, Nper.  This field represents the total number of installments you will be paying.  So if your loan is taken out over a 10 year period, you won't be making 10 payments in total, but rather 10 years of 12 months each, so a total of 120 payments.   Click once onto the cell that contains your term value, and then type *12

The final required field is PV.  This part of the calculation is where you indicate the PresentValue i.e. the loan amount that needs to be re-payed.  Ensure that your cursor is in the PV field, type a minus sign and then click once onto the cell on your spreadsheet that contains the loan amount.  Typing a minus sign is not required, but it's a clever way to ensure that the calculated monthly repayment does not appear as a negative value.
At this point you can click OK to see installment that will be required for this loan.  Please remember that many loan companies will have their own additional costs that will mean this will not be an exact amount but it will at least give you a good idea of what to expect.

There are however, two additional fields are not required in order for the calculation to work correctly, but they can have an impact on the answer.

The first of these fields is FV.  This is the FutureValue - the amount that the loan needs to reach by the end of the loan period.  This would typically be zero - you want to pay the loan off in full over the course of the loan period and if you leave this field blank, Excel will assume you wish to pay off the entire loan.  Of course this isn't always the case, and sometimes your loan may allow for what is known as a balloon payment / residual payment.  If you have such a balloon payment, you still enter the full loan amount into the PV field, and the balloon amount into the FV field.

The second optional field is Type.  This is used to indicate when payment is required - if you are required to pay at the end or beginning of the period.  Simply put, is your payment due at the end of the month or beginning.  If you leave this field blank, it is assumed you pay at the end of the month.  You can enter a 1 to indicate payment is due at the beginning of the period, whilst a 0 indicates payment at the end of the period.

I'm wondering if this post was heavy reading for you?  Would you prefer to have seen this tutorial as a 2 or 3 minute video clip?  Let me know you're thoughts

Using the Text to Columns wizard

To separate the data of one Excel cell into separate columns, you can use the 'Convert Text to Columns Wizard'. 

Using this example, I'll show you how to separate a list of full names into last and first names.

First, select all the data you want to separate, then on the Data tab of the ribbon, click Text to Columns

A wizard window will appear with some prompts that will guide you through this process.  The first of which is deciding if your data is Delimited or Fixed width.  Basically, Excel needs to know where the data should be split... should it chop the data after a fixed number of characters (Fixed width) or when it finds a specific character (Delimited).  In the case of names and surnames, you cannot predict how long a name will be, and therefore it makes sense to use Delimited.  

Click Next

Now you get to indicate what the delimiter is.  Check boxes have been created for the most common characters that act as delimiters.  In this example a space separates the name from the surname and has been checked as such.  If necessary, you can click the Other option and type in the delimiter.  

This screenshot shows an example of needing to use a colon (:) as the delimiter.

As soon as the delimiter is specified, you can see a preview of your data below. Click Next and then click Finish to see your newly separated data.

Another example is of data the suits a Fixed width delimiter.  This should only be used when you are certain of the number or characters you'd like between each section of data.

Once again, select all your data first, then on the Data tab of the ribbon click Text to Columns.  Choose Fixed width as your delimiter and click Next

You now create"break lines" which show where the data is to be separated by following the instructions at the top of the window.  Once you've indicated all the break lines click Next.

In the case of this example, I've separated the actual digits of the serial number away from the text "S/N" and actually I don't want the "S/N" heading anyway, so this step gives you the option to dump any columns of data you don't require.  Click the column that you don't want to keep (it'll be selected very clearly in black) and then click the option at the top of the window "Do not import column (skip)"

Click Finish to see your data.

That's it.  Quite an easy process once you get the hang of it.  Now you'll never need to manually edit those spreadsheets when next faced with a scenario like this.

Have a great week!

Paste data using the Transpose feature in MS Excel

I don't know about you, but I often lay out data on a spreadsheet and then change my mind about the orientation of it.  Thankfully there is a quick and easy way to get MS Excel data to swap places - basically putting all your row data into columns and your column data into the rows.

This is what your data may look like to begin with.

Highlight all the information and Copy.  You'll see the ubiquitous "sparkling lines" appear around the data to confirm you've copied successfully.  Now place your cursor into any other empty cell and choose Paste. I do this by right clicking over the cell, but you can also click the Paste button on the Home tab of the ribbon.

From the Paste Options list, select the Transpose icon.  That's it!  Your data will now appear transposed perfectly.

A quick note...you can't use the CTRL V shortcut key to paste as this will not show you the Paste Options buttons nor can you cut the original data and perform a Paste Transpose.

More Excel tips and tricks coming your way soon.  Let me know if there is anything in particular you're hoping to see.

My favourite ways to select text in MS Word

There are so many ways to select text, and we all have our favourites.... these are mine.

• a single word - double click over the word.
• an entire sentence  - Hold CTRL and click once anywhere on the sentence.
• the entire document - Press CTRL A
• a paragraph - triple click anywhere within the paragraph
• Irregular area - click once at the beginning of the area, hold your SHIFT key and then click at the end of the area to be selected

It's been a very MS Word focused set of tips over the last few weeks.  I hope you've found them useful, please share if you have.  I'll be working on some Excel tips next.