Lab-1: Lab Assignments

Lab-1: LAB ASSIGNMENTS
Q.1 DEVELOPMENT OF LOAN AMORTIZATION TABLE

In this exercise, we will learn how to use spreadsheets to solve simple accounting
problems. We will also learn how easy it is to use multiple formulas within Excel. The
handout is prepared with instructions to be used in Microsofts Excel 2007. Some of the
instructions are similar if an earlier version of Excel is used; however, it is required that
you use Excel 2007 or a later version for all the exercises in this class.
Loan Amortization tables show how much you owe a bank every month when you
borrow money from them. Let us assume that you recently graduated and bought a
brand new Ford Mustang (with cool white stripes, of course!). Thanks to government
bailouts you got a nice deal of $25,000 at an annual percentage rate (APR) of 6%, with
no down payment. You want to pay the loan within 5 years. In this exercise, you will
create an amortization table (monthly basis) that shows your payment, how much went
as interest, how much principal was paid, and the amount you owe to the bank. The
primary concept in preparing this table is that, each time you make a payment, you have
to pay interest on the outstanding borrowed amount, called Principal. The amount that
exceeds the interest amount paid reduces the principal before the next payment. Before
we proceed into the detailed calculations, we have to set up a spreadsheet for your
loan. We will design a general layout to organize information and make it easy to
understand.
Figure 1.1 Spreadsheets layout after setting the column width

Click on the cell A1 and type Loan Amortization Table. It can be observed that the cell
width is insufficient to fit the contents of the cell. To adjust the cell size, move the cursor
to the area shown in Figure 1.0 and click and drag to resize the cell width. Alternatively,
you can double click in the area to automatically adjust the cell width to fit the contents.
Make the font bold; this is the heading for the worksheet. Every worksheet should have
a heading; this is not a necessity, but it is recommended to keep the spreadsheet
organized. Click on cell A3 and type Loan Amount, APR in cell A4, Loan Term in
cell A5, Payments/year in the cell A6. These are called labels. If the worksheet is filled
with numbers, it will be difficult for the user to understand it. So, we give names to each
number to communicate with the end user what each number means. Always use
appropriate labels. Enter the value 25000 in cell B3, 6% in B4, 5 in B5 and finally 12 in
B6. We have now entered all input data needed for our calculation. Now we can
additionally format the cells to make them more presentable. Since the loan amount is
in dollars, we should tell Excel that the number in cell B3 is currency. This can be done
by right clicking on the cell B3 and then clicking on the Format cells option. Click on
the Numbers tab and choose Currency and click OK.
Figure 1.2 Formatting cells to display currency.
Figure 1.3 Loan amount displayed as currency.

As observed in Figure 1.3, the loan amount is displayed in currency format and hence
has a $ symbol before the value. You could also change the color and make the labels
bold. Play with the formatting a little to differentiate the input values from the end
results. Since we make the payments every month, it is necessary to calculate the
periodic interest rate. The APR is 6%; therefore, the interest rate for each month would
be (6/12) = 0.5%. The loan term is 5 years; hence the loan will be repaid in 12*5=60
payments. We should add this additional information into the spreadsheet with
appropriate labels. Assign different colors to the cells to differentiate the input, output
and titles. To change the color of a cell, select the cell and click on the button
highlighted by a red box in the Figure 1.4. Pick an appropriate color from the list.
Figure 1.4 After entering additional information (Black box on top shows the
cell color button)
Enter Total payments in cell A7 and in cell B7 enter the formula =B5*B6. Type
Periodic Interest Rate in cell A8 and in cell B8 enter the formula =B4/B6. To ensure
that your number displays as a percentage, Right click on cell B8, select Format Cells,
Click on the Number tab, Select Percentage and change the Decimal places to 1 as
shown in Figure 1.5. Finally we calculate the monthly payment amount from the periodic
interest rate, principal amount and total number of payments. The payment per month
can be calculated using the formula (which you will learn in your economics /finance
class):
Enter Payment amount in cell A9 and enter the formula =B3*B8/(1-((1+B8)^- B7)) in
cell B9. This number is currency, so follow the instructions as before and change the
cell to display the value as currency. Now the spreadsheet has all the data required to
build the amortization table.
Figure 1.5 Changing the numbers to percentage.

Now set up the spreadsheet to calculate the payment information for every month. This
should calculate the interest for each month based on the principal, the amount of
money paid towards the principal after subtracting the interest amount and finally the
principal amount after the payment. Set up the spreadsheet as shown in Figure 1.6.
Figure 1.6 Setting up the spreadsheet to calculate periodic payments.
To ensure that your text fits the cells, you should select the cells and right click on them
and choose the Format cells option, click on the Alignment tab and check the Wrap
text option. The text you entered into the selected cell will automatically fit into the cell.
Figure 1.7 Wrap text in the cell.

The principal amount for the first payment is simply the same amount that was originally
borrowed from the bank. So we will enter the formula =B3 in the cell C12. Now the
interest on this amount is calculated using the simple interest formula i.e. Principal
amount before payment * periodic interest rate. The periodic interest rate is the same
for every month; therefore, we must use an absolute address when we use the
periodic interest in the formula. By using the absolute address, we make sure that the
input value does not change when we copy the formula.
Remember, as we discussed in class, absolute addressing can done by explicitly typing
two dollars signs in front of a cell address or by pressing F4. Two dollar signs imply that
the cell copied along a row or column will have a fixed value. However, if you put just
one dollar sign in front of an address (e.g. $F8) then the column indicated by F will stay
constant but the row numbers will change. On the other hand if you put a dollar sign in
front of the number (e.g. F$8) then the row number 8 will remain constant and the
columns will change automatically when you copy.
Figure 1.8 Calculate the interest amount for each payment

We subtract the interest from the monthly payment of $483.32 to calculate the amount
paid on the principal. We will use absolute addressing when we reference the cell with
monthly payments, as this stays constant throughout the calculation.
Figure 1.9 Calculating the amount paid on principal

Finally, we subtract the amount paid on principal from the principal amount before
payment. This will be principal amount for the next months payment.
Figure 1.10 Calculating the principal after payment

This completes the calculation for the first payment. Now we can calculate the amount
of interest and principal paid by the second payment. The principal for the second
payment is equal to the principal amount after the first payment. So enter the formula
=F12 in C13.
Figure 1.11 Beginning the calculation for the second payment

To calculate the rest of the entries in this table, all we have to do is copy the formula for
cells D12 to F12. This can be done in a couple of ways: Select all cells from D12 to F12
and right click on them and select Copy. Next right click on the cell D13 and choose
Paste. This calculates all the entries for the second payment.
Figure 1.12 Calculating the second payment using the copy paste method
The second way to accomplish this is to select the cells for D12 to F12 and move your
mouse to the bottom corner of the selected cells as shown in Figure 1.13.
Figure 1.13 Calculating the second payment using the click and drag method
When the mouse arrow turns to the + button as shown in Figure 1.13, click and drag to
the next row. This will calculate all the entries for the next payment.
Figure 1.14 Calculating the entries for the second payment

Calculating the next 58 entries is a breeze, as we just have to copy the formula from
cells C13 to F13 and paste it in the next 58 cells either by using the copy-paste method
or the click-and-drag method. In the copy-paste method, select all the cells from
C14:F14 to C71:F71, right click on the cells and choose paste. However, when using
the click-and-drag method, all you have to do is to select the cells C13 to F13 and click
at the bottom corner, then drag down to C71:F71 cells. Excel will automatically fill all the
entries for the next 58 payments. Figure 1.15 shows the amortization table for the first
10 payments.
Figure 1.15 Completed loan amortization table for the first 10 payments.
Notice that the table was already configured to show the $ sign for the all entries.
Ensure that proper formatting is followed to improve the appearance and readability of
the table.
Try this:
Prepare the new amortization table for a loan amount of $30000, APR of 8% and a
loan term of 8 years. What is the total amount of interest you would pay to the
bank over the 8-year loan period?
Q.2 Produce a currency conversion table which converts from Euro to Pounds and
to Dollars, based on the conversion rate of 0.6884 Euro to Pounds and 1.2382
from Euro to Dollars. Write down your numbers with a precision of four digits.
Use the Autofill function to produce a column with 0.1-0.9, 1-10 and 10-100 (in
Euro). Provide a field for the date and produce it using the TODAY-function. Make
your table flexible such that when the convertion rate changes you only have to
alter the convertion rate and the table will be changed automatically. Add a title
saying Currency conversion table. Use your table to convert 58.7 Euros into
Pounds and Dollars. The table should look roughly as
Q.3

Lab-1: Lab Assignments

Uploaded by

Copyright:

Available Formats

Lab-1: Lab Assignments

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lab-1: Lab Assignments

Uploaded by

Copyright:

Available Formats

Lab-1: LAB ASSIGNMENTS

Q.1 DEVELOPMENT OF LOAN AMORTIZATION TABLE

Figure 1.1 Spreadsheets layout after setting the column width

Figure 1.2 Formatting cells to display currency.

Figure 1.3 Loan amount displayed as currency.

Figure 1.5 Changing the numbers to percentage.

Figure 1.6 Setting up the spreadsheet to calculate periodic payments.

Figure 1.7 Wrap text in the cell.

Figure 1.8 Calculate the interest amount for each payment

Figure 1.9 Calculating the amount paid on principal

Figure 1.10 Calculating the principal after payment

Figure 1.11 Beginning the calculation for the second payment

Figure 1.14 Calculating the entries for the second payment

You might also like