Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function in Excel
I am using the PMT function in MS-Excel-2003 with both a PV amount and a FV
amount in the function. I am trying to calculate a monthly loan payment where the loan amount has a residual after the last payment. The values I am using are as follows: Rate = 1.5% Nper = 24 PV = $120,000.00 FV = $37,000.00 Type = 1 The PMT function yields a $4,629.46 payment amount. When I calculate this out in a month-by-month table in Excel, the last payment needs to be almost $500 lower that this monthly payment amount, in order to result in a residual of $37,000 If a monthly payment amount of $4,612.39 is used in the month-by-month table, the last payment needs to be only a few cents different n order to result in a residual of $37,000 (1) Why does the PMT function not calculate the proper monthly amount with both a PV and FV amount in the function? (2) Is there a fix, or work-around to get the correct figure? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function in Excel
Hi Tim,
Could you post the formula you are using to calculate the month by month payment. Also, have you checked the way you are applying your formula against the Type parameter in PV? This makes a significant difference. Regards, OssieMac "Tim" wrote: I am using the PMT function in MS-Excel-2003 with both a PV amount and a FV amount in the function. I am trying to calculate a monthly loan payment where the loan amount has a residual after the last payment. The values I am using are as follows: Rate = 1.5% Nper = 24 PV = $120,000.00 FV = $37,000.00 Type = 1 The PMT function yields a $4,629.46 payment amount. When I calculate this out in a month-by-month table in Excel, the last payment needs to be almost $500 lower that this monthly payment amount, in order to result in a residual of $37,000 If a monthly payment amount of $4,612.39 is used in the month-by-month table, the last payment needs to be only a few cents different n order to result in a residual of $37,000 (1) Why does the PMT function not calculate the proper monthly amount with both a PV and FV amount in the function? (2) Is there a fix, or work-around to get the correct figure? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function in Excel
There is no error in the PMT function. When I build the amortization table, I
get exactly $37,000 at the end of 24 months. The most likely sources of error a 1. You aren't replicating the type function properly. A type of 1 means payments at the beginning of the month. You need to calculate the interest as =(OpenBal - Pmt) * 1.5% 2. Rounding or typos. PMT returns $4,629.26 (not 4,629.46) with the parameters given. And, don't round this number, otherwise you'll get small errors at the end. -- Regards, Fred "Tim" wrote in message ... I am using the PMT function in MS-Excel-2003 with both a PV amount and a FV amount in the function. I am trying to calculate a monthly loan payment where the loan amount has a residual after the last payment. The values I am using are as follows: Rate = 1.5% Nper = 24 PV = $120,000.00 FV = $37,000.00 Type = 1 The PMT function yields a $4,629.46 payment amount. When I calculate this out in a month-by-month table in Excel, the last payment needs to be almost $500 lower that this monthly payment amount, in order to result in a residual of $37,000 If a monthly payment amount of $4,612.39 is used in the month-by-month table, the last payment needs to be only a few cents different n order to result in a residual of $37,000 (1) Why does the PMT function not calculate the proper monthly amount with both a PV and FV amount in the function? (2) Is there a fix, or work-around to get the correct figure? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function in Excel
On Aug 13, 2:06 pm, Tim wrote:
The values I am using are as follows: Rate = 1.5% Nper = 24 PV = $120,000.00 FV = $37,000.00 Type = 1 [....] (1) Why does the PMT function not calculate the proper monthly amount with both a PV and FV amount in the function? (2) Is there a fix, or work-around to get the correct figure? I get the right answer, no matter what assumptions I make below. The PMT function yields a $4,629.46 payment amount. I presume that's a typo. I get 4629.26 when the month rate is 1.5% and payment is "in advance" (type=1). Alternatively, perhaps the monthly rate is between 1.500244% and 1.500256% (rounded), namely =rate(24,4629.46,-120000,37000). When I calculate this out in a month-by-month table in Excel, the last payment needs to be almost $500 lower that this monthly payment amount, in order to result in a residual of $37,000 If a monthly payment amount of $4,612.39 is used in the month-by-month table, the last payment needs to be only a few cents different n order to result in a residual of $37,000 I cannot duplicate your numbers. Please post the structure and formulas for your "month-by-month table" (aka amortization schedule). This is how I would structure it (without frills): B1: 4629.46 or =pmt(C1,24,-120000,37000,1) [monthly payment] C1: =rate(24,4629.46,-120000,37000,1) or 1.500250% [monthly interest] E1: 120000 [initial loan amount] A2: =A1+1 [payment number] B2: =B1 C2: =(E1-B2)*$C$1 [monthly interest] D2: =E1-E2 [monthly principal] E2: =E1+C2-B2 [monthly ending balance] Copy A2:E2 down through A25:E25. Notes: 1. Technically, B1 should be =roundup(pmt(...),2). Then: B2: =if(or(A2=24,E1+C2<B1), roundup(E1+C2,2), B1) E2: =max(0,E1+C2-B2) Thus, the last payment will likely be less than the other monthly payments. 2. Arguably, monthly interest might also be rounded (up?). That depends on the lender. Obviously, the lender rounds (up?) the amount of total interest reported on the 1099-INT at the end of year (for US loans). But that does not necessarily dictate the lender's internal computations. 3. Are you sure that the payment is "in advance", not "in arrears" (type=0 or omitted)? For most loans that I've seen, payment is "in arrears", with "prepaid interest" added to the closing cost to cover the short (irregular) period at the beginning. In that case, interest (C2) is =E1*$C$1, copied down. But that does not result in the unusual numbers that you post ("almost $500" and $4,612.39) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function in Excel
Thanks for the reply OssieMac,
Attached is a link to my Excel file which contains the PMT formula, and the table I used to verify the formula. http://www7.sendthisfile.com/d.jsp?t...kcARsCjkUKJjV8 In this Excel file you will find 2 Tabs, one Tab where the PMT Type = 1 (& PMT result is wrong) and one Tab where the PMT type = 0 (where PMT results is correct) "OssieMac" wrote: Hi Tim, Could you post the formula you are using to calculate the month by month payment. Also, have you checked the way you are applying your formula against the Type parameter in PV? This makes a significant difference. Regards, OssieMac "Tim" wrote: I am using the PMT function in MS-Excel-2003 with both a PV amount and a FV amount in the function. I am trying to calculate a monthly loan payment where the loan amount has a residual after the last payment. The values I am using are as follows: Rate = 1.5% Nper = 24 PV = $120,000.00 FV = $37,000.00 Type = 1 The PMT function yields a $4,629.46 payment amount. When I calculate this out in a month-by-month table in Excel, the last payment needs to be almost $500 lower that this monthly payment amount, in order to result in a residual of $37,000 If a monthly payment amount of $4,612.39 is used in the month-by-month table, the last payment needs to be only a few cents different n order to result in a residual of $37,000 (1) Why does the PMT function not calculate the proper monthly amount with both a PV and FV amount in the function? (2) Is there a fix, or work-around to get the correct figure? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function in Excel
JoeU2004 - thanks for the Reply
Attached is a link to my Excel file which contains the PMT formula, and the table I used to verify the formula. http://www7.sendthisfile.com/d.jsp?t...kcARsCjkUKJjV8 In this Excel file you will find 2 Tabs, one Tab where the PMT Type = 1 (& PMT result is wrong) and one Tab where the PMT type = 0 (where PMT results is correct) "joeu2004" wrote: On Aug 13, 2:06 pm, Tim wrote: The values I am using are as follows: Rate = 1.5% Nper = 24 PV = $120,000.00 FV = $37,000.00 Type = 1 [....] (1) Why does the PMT function not calculate the proper monthly amount with both a PV and FV amount in the function? (2) Is there a fix, or work-around to get the correct figure? I get the right answer, no matter what assumptions I make below. The PMT function yields a $4,629.46 payment amount. I presume that's a typo. I get 4629.26 when the month rate is 1.5% and payment is "in advance" (type=1). Alternatively, perhaps the monthly rate is between 1.500244% and 1.500256% (rounded), namely =rate(24,4629.46,-120000,37000). When I calculate this out in a month-by-month table in Excel, the last payment needs to be almost $500 lower that this monthly payment amount, in order to result in a residual of $37,000 If a monthly payment amount of $4,612.39 is used in the month-by-month table, the last payment needs to be only a few cents different n order to result in a residual of $37,000 I cannot duplicate your numbers. Please post the structure and formulas for your "month-by-month table" (aka amortization schedule). This is how I would structure it (without frills): B1: 4629.46 or =pmt(C1,24,-120000,37000,1) [monthly payment] C1: =rate(24,4629.46,-120000,37000,1) or 1.500250% [monthly interest] E1: 120000 [initial loan amount] A2: =A1+1 [payment number] B2: =B1 C2: =(E1-B2)*$C$1 [monthly interest] D2: =E1-E2 [monthly principal] E2: =E1+C2-B2 [monthly ending balance] Copy A2:E2 down through A25:E25. Notes: 1. Technically, B1 should be =roundup(pmt(...),2). Then: B2: =if(or(A2=24,E1+C2<B1), roundup(E1+C2,2), B1) E2: =max(0,E1+C2-B2) Thus, the last payment will likely be less than the other monthly payments. 2. Arguably, monthly interest might also be rounded (up?). That depends on the lender. Obviously, the lender rounds (up?) the amount of total interest reported on the 1099-INT at the end of year (for US loans). But that does not necessarily dictate the lender's internal computations. 3. Are you sure that the payment is "in advance", not "in arrears" (type=0 or omitted)? For most loans that I've seen, payment is "in arrears", with "prepaid interest" added to the closing cost to cover the short (irregular) period at the beginning. In that case, interest (C2) is =E1*$C$1, copied down. But that does not result in the unusual numbers that you post ("almost $500" and $4,612.39) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function in Excel
I'm sorry you couldn't figure out the problem from the responses given.
Your problem (with Type=1) is that you have 24 payments, but you have only 23 periods with interest charged. Add one more month of interest, and you will get exactly $37,000. My other recommendation is you redesign the spreadsheet to avoid the disjointed periods. When Type=1, calculate interest as IntRate * (Bal - Pmt). -- Regards, Fred "Tim" wrote in message ... JoeU2004 - thanks for the Reply Attached is a link to my Excel file which contains the PMT formula, and the table I used to verify the formula. http://www7.sendthisfile.com/d.jsp?t...kcARsCjkUKJjV8 In this Excel file you will find 2 Tabs, one Tab where the PMT Type = 1 (& PMT result is wrong) and one Tab where the PMT type = 0 (where PMT results is correct) "joeu2004" wrote: On Aug 13, 2:06 pm, Tim wrote: The values I am using are as follows: Rate = 1.5% Nper = 24 PV = $120,000.00 FV = $37,000.00 Type = 1 [....] (1) Why does the PMT function not calculate the proper monthly amount with both a PV and FV amount in the function? (2) Is there a fix, or work-around to get the correct figure? I get the right answer, no matter what assumptions I make below. The PMT function yields a $4,629.46 payment amount. I presume that's a typo. I get 4629.26 when the month rate is 1.5% and payment is "in advance" (type=1). Alternatively, perhaps the monthly rate is between 1.500244% and 1.500256% (rounded), namely =rate(24,4629.46,-120000,37000). When I calculate this out in a month-by-month table in Excel, the last payment needs to be almost $500 lower that this monthly payment amount, in order to result in a residual of $37,000 If a monthly payment amount of $4,612.39 is used in the month-by-month table, the last payment needs to be only a few cents different n order to result in a residual of $37,000 I cannot duplicate your numbers. Please post the structure and formulas for your "month-by-month table" (aka amortization schedule). This is how I would structure it (without frills): B1: 4629.46 or =pmt(C1,24,-120000,37000,1) [monthly payment] C1: =rate(24,4629.46,-120000,37000,1) or 1.500250% [monthly interest] E1: 120000 [initial loan amount] A2: =A1+1 [payment number] B2: =B1 C2: =(E1-B2)*$C$1 [monthly interest] D2: =E1-E2 [monthly principal] E2: =E1+C2-B2 [monthly ending balance] Copy A2:E2 down through A25:E25. Notes: 1. Technically, B1 should be =roundup(pmt(...),2). Then: B2: =if(or(A2=24,E1+C2<B1), roundup(E1+C2,2), B1) E2: =max(0,E1+C2-B2) Thus, the last payment will likely be less than the other monthly payments. 2. Arguably, monthly interest might also be rounded (up?). That depends on the lender. Obviously, the lender rounds (up?) the amount of total interest reported on the 1099-INT at the end of year (for US loans). But that does not necessarily dictate the lender's internal computations. 3. Are you sure that the payment is "in advance", not "in arrears" (type=0 or omitted)? For most loans that I've seen, payment is "in arrears", with "prepaid interest" added to the closing cost to cover the short (irregular) period at the beginning. In that case, interest (C2) is =E1*$C$1, copied down. But that does not result in the unusual numbers that you post ("almost $500" and $4,612.39) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function in Excel
On Aug 15, 8:04 am, Tim wrote:
Attached is a link to my Excel file which contains the PMT formula, and the table I used to verify the formula. http://www7.sendthisfile.com/d.jsp?t...kcARsCjkUKJjV8 In this Excel file you will find 2 Tabs, one Tab where the PMT Type = 1 (& PMT result is wrong) The mistake in the Type=1 worksheet is that you are not computing interest correctly. For example, you have zero interest in the first period. I used to do the same thing. But in fact, interest is computed based on the previous balance (initial loan amount) less the payment, as indicated for C2 in my previous posting. The proof of this can be demonstrated with the following formula: =fv(1.5%, 1, 4629.26, -120000, 1) That computes the outstanding balance after the first payment "in advance". Note that the result is 117,101.30 (rounded), not 115,370.74 (120000 - 4629.26). As I noted in my previously posting (adapted to your worksheet), interest should be computed as follows in C24 and copied down through C47: C24: =(E23-D24)*$C$11 (Also note that the payment is indeed 4629.26, rounded, in your worksheet, not 4629.46 as you wrote in your initial posting.) PS: The annual interest rate is 18% (12*1.5%), not 19.5620%, which you compute in C12 by (1+1.5%)^12-1. The latter computes the APY of an investment, not the APR of a loan. and one Tab where the PMT type = 0 (where PMT results is correct) Whether the payment is "in advance" or "in arrears" depends on the terms of the loan. In both cases, the financial math should yield the correct results. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: Auto Sum function not working in large Excel file | Excel Discussion (Misc queries) | |||
challenge! javascript function into excel function | Excel Worksheet Functions | |||
FUNCTION GETPIVOTDATA MICROSOFT EXCEL 2003 VS EXCEL 2004 FOR MAC | Excel Worksheet Functions | |||
Excel Workday Function with another function | Excel Discussion (Misc queries) | |||
Can you nest a MID function within a IF function in Excel | Excel Worksheet Functions |