Microsoft Office Tutorials and References
In Depth Information
Tutorial C: Building a Decision Support System in Excel
When you click OK, the resulting window allows you to enter the cells or values needed in the function
arguments (see Figure C-10). In the Rate text box, enter B21, which is the cell that will contain the calculated
interest rate. In the Nper text box, enter 10 (for 10 years). In the Pv text box, enter C5, which is the cell that
contains the loan amount.
The Function Arguments dialog box for the PMT function with the values filled in
Be careful if you decide to copy the PMT formula from cell C32 into cell D32, because the Copy command will change the cells
in the formula arguments to the next adjacent cells. To make the Copy command work correctly, you have two options. First,
you can change the Rate and Pv cells in the cell C32 formula from relative reference (B21, C5) to absolute reference ($B$21,
$C$5). Your other option is to re-insert the PMT function into cell D32 and type the same arguments as before in the boxes.
Absolute referencing of a cell (using $ signs in front of the Column and Row designators) “anchors” the cell so that when the
Copy command is used, the destination cell will refer back to the same cells that the source cell used. If necessary, consult the
Excel online Help for an explanation of relative and absolute cell references.
When you click OK, ($10,000) should appear in cell C32. Payments in Excel always appear as negative
numbers, which is why the number has parentheses around it. (Depending on your cell formatting, the
number may also appear in red.) Next, you need to have the same payment amount in cell D32 (for 2013).
Because the PMT function creates equal payments over the life of the loan, you can simply type =C32 into
The next line in the Income and Cash Flow Statements is Income before Taxes, which is an easy
calculation. It is the Sales minus the Cost of Goods Sold, minus the Business Loan Payment. However, because the
PMT function shows the loan payment as a negative number, you will instead add the Business Loan Payment.
In cell C33, enter =C30-C31+C32. Again, a negative $10,000 should be displayed, as the cells other than the
loan payment currently have zero in them. Copy cell C33 to cell D33. In cell C42 of the next section below
(No Expansion), enter =C40-C41. (There is no loan payment in this section to put in the calculation.) Next,
copy cell C42 to cell D42. At this point, your Income and Cash Flow Statements should look like Figure C-11.