Saturday, 21 September 2013

Excel Financial Functions (PMT)

Excel Financial Functions

 The PMT function, one of many Excel's Financial functions, it can be used to calculate loan repayments or the future value of an investment. This Topic covers using the PMT function to determine loan repayment



The syntax for the PMT function is: = 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 2010's PMT Function to Calculate Loan Payments:




     
    Enter the following data into cells:
     
     
    B 2- Rate:
    B3- No of payments:
    B4 - Loan Amnt:
    B5 - Payment:
    C2 - 7%
    C3 - 24
    C4 - £10,000.00
     
    Click on cell C5 - the location where the results will be displayed.  
    Click on the Formulas tab.








    Choose Financial functions > PMT from the ribbon to bring up the function's dialog box.



     
    Click on the Rate line in the dialog box.Click on cell C2 in the spreadsheet

    .After the C2, 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.

    Click on the Nper line in the dialog box, Click on cell C3 in the spreadsheet..

    Click on the Pv line in the dialog boxClick on cell C4 in the spreadsheet..
    Click OK in the dialog box.
     
    The answer -£447.73 appears in cell C5 the value shown is a negative number as this is a debit, to show a posative figure, Type a minus sign ( - ) and then click on cell C4 in the spreadsheet.  
     the complete function as it apears in the formula bar =PMT(C2/12,C3,C4) 

No comments:

Post a Comment