Financial Calculation Software, a Snap with MS Excel - A simple spread sheet to calculate PMT
(Page 4 of 4 )
If this is all the program can do, it is not interesting and you do not even need a spread sheet. It is possible to transfer values entered into spread sheet cells to be automatically entered into the interactive Function Arguments dialog as described in this section.
In the work sheet for this calculation, set up the cells as shown in the next picture. In cells c2, c3, and c4 enter the input quantities (arguments for the PMT function), 8%/12, 10, and 10000. If you just type in 8%/12 it will be assumed to be string; enter it as =(8%/12), then the calculated value you see in the next picture is entered in c2.
Cell c8 is where you want your calculated value of PMT to appear. Place your cursor in this cell, and from the main kick up the Insert function dialog as described earlier. Choose to use the PMT function, which brings up the interactivedialog. Now click on the button shown in the next picture.
This brings up the next dialog as shown in this picture. Now click into cell C2 so that the value in the cell is transferred to the argument cell pointed by this window. After clicking in C2 click on the button as shown here.
This brings back the interactive dialog. A partial cutout of the screen is shown in the next picture. Whatever has been entered is shown in this picture. Continue in the same manner to enter the three values. You may skip this step for fv and type so that the program may assume defaults.
Now the final value is entered and the screen shows the cells in the arguments' locations rather than hard coded values. Clicking OK will show the result of this calculation in the cell C8. Even before clicking OK, the value is already calculated as shown in the next picture, and clicking OK will insert it into cell C8. Now we can go ahead and change the values in the cell (to test what if scenarios, such as what if the interest rate is 8.5% instead of 8%?) and it will be immediately calculated in cell C8. For example, if you pay off the loan over 12 months instead of 10 months you need only pay $869.88 per month.
The next picture shows two cases where payments were made at the beginning or end of the pay period. Notice the values for Type entered in the cells C6 or C7.
Summary
As demonstrated here MS Excel is truly a RAD tool for financial calculations. As mentioned earlier there are more than 50 functions available that will increase productivity in the work place. The online help is extremely useful. Combined with the full power of the Office products, it is no wonder that it maintains its premier place in the industry.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
