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.