Car+Buying+Spreadsheet+Formulas

Software: Microsoft Excel[[image:funky-car.jpg width="240" height="180" align="right"]]
What to do: Create a new Microsoft Excel worksheet like the one below. Microsoft Excel Worksheet designed to calculate interest and total payment

Microsoft Excel worksheet designed to calculate interest and total payment for a puchase, based on different loan terms.

Follow these time-saving tips:

Worksheet Titles: To create a bold face and centered worksheet title, type the title in column A and press Enter. Highlight columns A through E and then click the Merge and Center button on Microsoft Excel's Formatting toolbar. Click the Bold button. Increase the font size of the text to 14.

Word Wrap: Enter cell contents in column A. To turn on word wrap, highlight all cells in which text must wrap (A6:A12) then right-click the highlighted cells. Click the Alignment tab, then select Wrap text in the Text control section.

Column Width: To adjust column width, double click the column boundary to the right of a column heading. To create columns of uniform width, select those columns, choose Column from the Format menu, choose Width, then enter a value, e.g. 14.

Formatting Currency: Select cells by dragging mouse over cells and then pressing the Ctrl key while clicking the other cells. Right-click on a highlighted cell, then choose Format Cells from the pop-up menu. Click the Number tab and choose Currency with 2 decimal places.

Step B
Add Formulas Software: Microsoft Excel What to do : PMT Function: In the spreadsheet next to Amount Paid Each Payment Period choose Function from the Insert menu. Select Financial from the Function Category list, then choose PMT from the Function Name list. Click OK.

For Rate (the interest rate per period for the loan) Interest rate/ Length of Loan

for Nper (the total number of loan payments) Length of loan (in years * number of payments

for Pv (loan amount) Loan amount Choose OK.

Microsoft Excel returns a negative number to indicate that you are making a payment.

"Total Amount Paid" Formula: =SUM (the amount you pay each month(length of loan * number of payments)) In otherwords =SUM(B10*(B8*B9))

"Interest Paid" Formula:

=SUM (the amount you paid- the interest for the loan),which is the total amount paid minus the loan amount. If cells have been formated for a positive amount use this formula =SUM (the amount you paid+ the interest for the loan)

Showing Formulas: To have Microsoft Excel show formulas, rather than formula results, choose Options from the Tools menu; click the View tab, then select the Formulas check box. To display formula results, clear the check box.