Use Excel for Everday Problems
The latest version of Excel from Microsoft 2016 is better than ever. Use Excel for Everday Problems!
Everyone know you can use Excel to build that gigantic company financial analysis sheet, and as such it is a fantastic business tool. Excel is also good for finding a quick solution for everyday problems at home such as loans and much more. The sky is really the limit if you will just take the time to explore all the capabilities Excel has to offer.
Consider using a built-in Templates
When you open Excel 2016 you will be presented with the option of opening a blank worksheet or using a predefined template. Several are built right in or you can search online for just the one for you.
Financial Formulas – PMT (Payment)
Considering a loan for that new car or maybe a home? The PMT formula is just what you need and is easy to use. You can create a quick spreadsheet to analyze all your loan options:
For any simple loan, the factors are the:
- Amount of the loan (principle)
- Interest Rate (annual – but remember to divide the interest rate by 12 assuming you make monthly payments)
- The term of the loan in months
The PMT formula in Excel can handle future value in the event you don’t want to pay the loan off completely. The type of loan in Excel refers to making payments at the end (use 0) or at the beginning of each period (usually each month) (use 1). The formula is =pmt(rate,nper,pv,[fv],[type]). Where rate is the annual interest rate, the pv is the amount of the loan, and nper is the number of periods, usually months.
By changing the values of in your worksheet you can immediately see the effect of even small changes. In the example below you can quickly see the effect of changing the interest rate by 1/10th of a percentage point (Cell values in C2 through C4) on my $25,000 loan payable over 2 years (or 24 months). Immediately excel will calculate your new monthly payment.
Once you get the hang of this, you can quickly create a loan spreadsheet you can save and use for any loan situation!
Picking Random Names from a List
You can also use Excel for manipulation on text strings. There are many formulas dealing with text strings, take a few min to explore. Let’s say you have a list of all your employees on a sheet, and you want to choose a random name from the list for the “Employee Of The Month“. You could put all the names in a basket and have someone pick a name but what fun is that. There are several ways to do this. One is to assign a number to each person on the list and use the RANDBETWEEN, to have Excel randomly select a number between a range of numbers you specify. But if you take the time you can find more elegant ways to solve problems with Excel. For this example use this this formula:
=INDIRECT(CONCATENATE(“A”,RANDBETWEEN(2,12)))
This example is shown below, where “A” is the column that contains the list of names, and (2,12) is the beginning and ending row number.
Once you have this setup, just press F9 to request a new name from the list!