Not only is my loan calculator a useful application, but from a training point of view it demonstrates a number of useful formulas. As an example, imagine that you want to purchase a computer. The computer store will provide you with a quote which is valid for 14 days, giving you time to make your mind up.

**B5** contains the function **=TODAY()**. This function puts the computer’s date into the cell. Be aware that if the file is saved and re-opened on another day, the date in B5 will be updated to display the current date. Therefore if you want the cell to retain the originally-entered date don’t use the =TODAY function.

**B6** contains the formula **=B5+14** which adds 14 days onto the date in B5 (the quote is valid for 14 days).

**B12** contains the formula **=B9-B10-B11** which calculates the loan required (the purchase price less a deposit less a trade-in value – this computer company will purchase your old computer if it’s in a good condition).

**B18** contains the formula **=EDATE(B17,B15)**. The EDATE function calculates the date that is a defined number of months away from another date. It requires 2 arguments – the starting date and the number of months.

**B21** uses the PMT function to calculate the monthly payment **=ABS(PMT(B14/12,B15,B12))**. More information about the PMT function and the ABS function can be found here.

**B22** contains a formula **=B21*B15** which multiplies the monthly payment by the number of months to calculate the true cost of the loan.

**A26** contains a formula that generates a text string. You can see the result of this in the screenshot.

**=B15 & ” payments of ” & TEXT(B21,”£0.00″) & ” will be taken from your account starting on ” & TEXT(B17,”dd mmmm yyyy”)**

The formula combines:

- The value in B15 (number of payments)
- The words
*payments of*(note the spaces at the beginning and end are part of the string) - The monthly payment, displayed as UK currency with 2 decimal places, converted to text (in order to be part of a text string)
- The words
*will be taken from your account starting on*(note the spaces at the beginning and end are part of the string) - The start date of the loan, displayed in month day year format, converted to text (in order to be part of a text string)

There is now a video tutorial available that covers the above PLUS additional features.