How to Calculate Simple Interest in Excel
Microsoft's Excel program is a useful tool for spreadsheets and other office uses. It can even calculate simple interest--that is interest that is accrued on a fixed amount--with a few easy steps. Taking the time to learn a little bit more about this important Excel feature is an easy way to increase your productivity.
Things You'll Need
- Microsoft's Excel
Start the program. Click on Start, then All Programs, then Microsoft Office, then Microsoft Office Excel.
Set Up the cells. Excel treats each box, or "cell," as a standing figure or set of instructions that relate to the other cells. The cells are labeled with letters and numbers based on the column (up and down) and row (left to right). In cell A1 type "Principal" (the amount bearing interest), in A2 type "Interest" (the amount charged on the principal), in A3 type "Amoritization Period" (the period of the loan), in A4 type "Monthly Payment." In cell A5 type "total cost of loan" and in A6 type "interest charged."
Instruct the program. Now that you have the labels set up, you need to tell the program what to do with this information. In cell B4 you will type: =PMT(B2/12,B3*12,B1,,0)This tells Excel that the payment (PMT) is equal to the amount in cell B2 divided by 12 (months), and that cell B3 is multiplied by 12, and finally that B1 is a fixed constant. In order to get the amount calculated, we need to jump over to other cells where the program will calculate everything and place the appropriate numbers where they need to be. In cell B5 type "=B4*B3*-12"In cell B6 type "=B5-B1"In cell B10 type "Payment #"in cell C10 type "Payment" in cell D10 type "Interest" in cell E10 type "Paydown"in cell F10 type "Balance O/S"
Set the algorithm. You need to enter the algorithm for Excel to calculate the interest. In cell B11 type "0" (zero), in cell F11 type "=B1" (this cell's value is equal to the value entered in B1), in cell B12 type "B11 1" in cell C12 type "=$B$4" in cell D12 type "F11*$B$2/12" in cell E12 type "C12-D12" and in cell F12 type "F11-E12"This are the instructions for one payment on your loan (the number you will enter for line B1) and you will need to know how many payments are going to be in the Amortization Period (let us say your loan is five years, so the number of payments will be 5 times 12 or 60 months).
Enter the numbers. In cell B1 you type the loan amount (for example, type "50000" for $50,000)In cell B2 type the interest (for example 4% or 8%)In cell B3 type the number of years the loan is for (5, 10, etc.)Once these numbers are in place, the long string of gibberish-seeming numbers and letters will be replaced with the loan payment you must pay based on the numbers you've added in cells B1, B2 and B3.
Tips & Warnings
- When typing information in a cell in Excel, remove all quotation marks.