Excel’s Financial Functions By Nate Moore, CPA, MBA, CMPE xcel has a variety of built-in functions to calculate loan payments, lease payments, and other financial transactions. It is easy to calculate interest rates, the present E value (how much a series of future payments is worth today), and future values (how much a series of future payments will be worth in the future) with Excel’s functions. The PMT Function to start, consider a loan for $100,000 with an interest rate of 5.6% requiring annual payments due at the end of the year for the next 10 years. to figure out the amount of the payment required on the loan, use excel’s Pmt function. the structure of the Pmt function is =PMT(Rate, Nper, PV, FV, Type). We will discuss each of the components of the Pmt function in order. “rate” is the interest rate per period on the loan. since the interest rate is 5.6% and payments are made annually, rate is .056 in our example. the rate needs to correspond to the same period of time as the number of payments in the loan calculation. for example, if the payments were made monthly instead of annually, rate would be .056/12. “nper” is the number of payments that will be made on the loan. in our example, the loan requires annual payments for 10 years, so nper is 10. again, the number of payments has to correspond to the interest rate. if the loan required monthly payments, nper would be 10*12= 120. “Pv” is the present value, or current worth, of the future stream of the 10 annual payments scheduled to be repaid on the loan. in our example, the loan pays out $100,000 today, so the present value is $100,000. “fv” is the future value of the amount of the loan after the last payment is made. in our example, the loan will be completely repaid with the last payment, so the future value is 0. if the loan had a balloon payment of $20,000 due at the end of the ten years of payments, the future value would be $20,000. fv is optional. if you do not enter a value for fv, excel assumes it to be 0. notice that excel uses positive and negative signs to keep track of whether the money is flowing from the lender to the borrower or from the borrower to the lender. it does not matter whether the money flowing from the lender to the borrower is shown with a positive sign or a negative sign, as long as all money flowing from the lender to the borrower uses a consistent sign. in other words, if the Pv is entered as a positive number, the Pmt function will return the payment with a negative sign. excel is not saying the payment amount is a negative number, it is simply saying the payment is going in the opposite direction of the Pv: the Pv is going from the lender to the borrower, and the payments flow from the borrower to the lender. if the Pv is entered as a negative number, the Pmt function will return the payment as a positive number. this can be very confusing at first. the key is to use positive and negative numbers in the Pmt, Pv, and fv values in all excel financial functions to consistently indicate the direction the money is flowing, from the lender to the borrower or the borrower to the lender. “type” is the last argument in the Pmt function and is either 1 or 0. if type is equal to 0 or is not entered, excel treats the payments as being due at the end of the period (month, year, etc.). a common example when type is equal to 0 is loan payments due at the end of each month. if type is equal to 1, excel treats the payments as being due at the beginning of the period, which is common for leases. type is an optional argument. excel assumes that payments are due at the end of the period unless type is specifically set to 1. Putting It All Together now that we understand the components of the Pmt function, we can create a formula to calculate payments on the hypothetical loan for $100,000 with an interest rate of 5.6% with annual payments due at the end of the year for the next 10 years. the formula is =PMT(0.056,10,100000,0,0), which calculates 10 annual payments of $13,330.49 to repay the loan. remember that the last two arguments in the formula are optional. since the future value (fv) is zero and the loan is due at the end of each year, the formula can be rewritten as =PMT(0.056,10,100000) to get the same result. to figure out the amount of the payment if the loan called 40 hbma billing • november.december.2013
Billing_ND13
To see the actual publication please follow the link above