Case+Study+2

There are a lot of formulas in the next case study but most importantly are the new financial functions one being how to calculate payment options (PMT) The functions or formulas can easily be made up by thinking and drawing out exactly what it is you want to calculate fist and then take that one step at a time. The following example is from the about.com spreadsheet help. I found it very helpful

PMT function (this is an example from the website: http://spreadsheets.about.com/od/excelfunctions/qt/20071020.htm )

The PMT function one of Excel's Financial functions, can be used to calculate the payments for a loan or the future value of an investment.

= PMT ( rate, nper , pv , fv , type )

rate - the annual interest rate for the loan.

nper - the total number of payments for the loan.

pv - the present value or the amount borrowed or the "principal of the loan.

fv - future value - for a loan this will be $0.00. For loans this argument can be omitted.

type - indicates when payments are due:
 * "0" (or omitted) - at the end of the period ie: end of the month.
 * "1" - at the beginning of the period ie: beginning of the month.

Example Using Excel 2007's PMT Function to Calculate Loan Payments:

Note: In this example, both the future value and type arguments have been omitted (see the image to the right).

> > D2 - Rate: > D3 - # of payments: > D4 - Principal: > D5 - Payment: > E2 - 7% > E3 - 24 > E4 - $5,000.00
 * 1) Enter the following data into cells:
 * 1) Click on cell E5 - the location where the results will be displayed.
 * 2) Click on the //Formulas// tab.
 * 3) Choose Financial functions > PMT from the ribbon to bring up the function's dialog box.
 * 4) Click on the //Rate// line in the dialog box.
 * 5) Click on cell E2 in the spreadsheet.
 * 6) After the E2, type a forward slash " / " followed by the number 12 in the //Rate// line of the dialog box. This gives you the interest rate per month.
 * 7) Click on the //Nper// line in the dialog box.
 * 8) Click on cell E3 in the spreadsheet.
 * 9) Click on the //Pv// line in the dialog box.
 * 10) Type a minus sign ( - ) and then click on cell E4 in the spreadsheet.
 * 11) Click OK in the dialog box.
 * 12) The answer $223.86 appears in cell E5.
 * 13) When you click on cell E5 the complete function = PMT ( E2/12, E3 , - E4 ) appears in the formula bar above the worksheet

Hope this works for everyone. Good luck tomorrow !