| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PPMT Function Repost
Thank you both (Fred Smith and JoeU2004) for your responses. My apologies for
replying so late therefore have lost the thread! What I am trying to compile is an Annuity Template where the user enters the various parameters and all calculations are compiled automatically. How I picked up the problem is that I have the following loan constants as an example on which the template is being built. 120,000,000 PV - Total loan 30,000,000 FV - Balloon 8.25% i - Annual Interest 12 n - Monthly payments - Interest calculated at 8.25%/12 1 Jan 00 Start date 31 Dec 09 End date - Baloon payment date + last instalment 10 Term loan - 10 year repayment period 120 NPer - Periods 1 PPMT type - in advance If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? The exercise is actually involved within the shipping industry where Bareboat (Lease) Charters have to be paid monthly in advance. Thanks in advance/sgl |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
"sgl" wrote:
If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? I tried to explain that before. By "total repayments", you mean the sum of the PPMT results. PPMT is assuming that the first payment applies entirely against principal, whereas standard loan functions (and the mathematics behind them) apply some of the payment toward interest in advance for the period. Alternatively, you can use the formula that I provided in the previous thread, with a tweak, to compute the principal paid down between any two periods, i and k, out of n periods (i <= k <= n). Namely: =fv(r,k,pmt,pv,fv,1) - fv(r,i-1,pmt,pv,fv,1) where r is periodic interest rate corresponding to the payment frequency, and pmt is PMT(r,n,pv,fv,1). This assumes that pv is positive and fv is negative; ergo, pmt is also negative. (In the real world, the PMT function result is rounded to cents, at least. But that will just add to the confusion because in that case, we do not expect things to add up properly anyway.) JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). Yes; and there is nothing wrong with that. The same amount of principal is paid down each period for payment in arrears and in advance. The only difference is the amount of interest paid down and, therefore, the installment payment itself. What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? I do not know what you did wrong, since you neglected to show your formula or method. The amount of interest paid between any two periods, i and k, out of n periods (i <= k <= n), can be computed by: =-pmt*(k - i + 1) - fv(r,k,pmt,pv,fv,1) + fv(r,i-1,pmt,pv,fv,1) That is: the sum of the payments less the principal paid down, expressed as a non-negative number like IPMT. (The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. I prefer to express all numbers as non-negative values and make the necessary adjustments when using the financial functions. Let me know if you would like me to restate everything in non-negative terms.) It might be easier for you to understand all this if you created an amortization schedule. HTH. ----- original message ----- "sgl" wrote in message ... Thank you both (Fred Smith and JoeU2004) for your responses. My apologies for replying so late therefore have lost the thread! What I am trying to compile is an Annuity Template where the user enters the various parameters and all calculations are compiled automatically. How I picked up the problem is that I have the following loan constants as an example on which the template is being built. 120,000,000 PV - Total loan 30,000,000 FV - Balloon 8.25% i - Annual Interest 12 n - Monthly payments - Interest calculated at 8.25%/12 1 Jan 00 Start date 31 Dec 09 End date - Baloon payment date + last instalment 10 Term loan - 10 year repayment period 120 NPer - Periods 1 PPMT type - in advance If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? The exercise is actually involved within the shipping industry where Bareboat (Lease) Charters have to be paid monthly in advance. Thanks in advance/sgl |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
Thanks for the reply, will give it a try tomorrow and come back to you, too
late this side of the world. Many tahnks/sgl "JoeU2004" wrote: "sgl" wrote: If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? I tried to explain that before. By "total repayments", you mean the sum of the PPMT results. PPMT is assuming that the first payment applies entirely against principal, whereas standard loan functions (and the mathematics behind them) apply some of the payment toward interest in advance for the period. Alternatively, you can use the formula that I provided in the previous thread, with a tweak, to compute the principal paid down between any two periods, i and k, out of n periods (i <= k <= n). Namely: =fv(r,k,pmt,pv,fv,1) - fv(r,i-1,pmt,pv,fv,1) where r is periodic interest rate corresponding to the payment frequency, and pmt is PMT(r,n,pv,fv,1). This assumes that pv is positive and fv is negative; ergo, pmt is also negative. (In the real world, the PMT function result is rounded to cents, at least. But that will just add to the confusion because in that case, we do not expect things to add up properly anyway.) JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). Yes; and there is nothing wrong with that. The same amount of principal is paid down each period for payment in arrears and in advance. The only difference is the amount of interest paid down and, therefore, the installment payment itself. What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? I do not know what you did wrong, since you neglected to show your formula or method. The amount of interest paid between any two periods, i and k, out of n periods (i <= k <= n), can be computed by: =-pmt*(k - i + 1) - fv(r,k,pmt,pv,fv,1) + fv(r,i-1,pmt,pv,fv,1) That is: the sum of the payments less the principal paid down, expressed as a non-negative number like IPMT. (The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. I prefer to express all numbers as non-negative values and make the necessary adjustments when using the financial functions. Let me know if you would like me to restate everything in non-negative terms.) It might be easier for you to understand all this if you created an amortization schedule. HTH. ----- original message ----- "sgl" wrote in message ... Thank you both (Fred Smith and JoeU2004) for your responses. My apologies for replying so late therefore have lost the thread! What I am trying to compile is an Annuity Template where the user enters the various parameters and all calculations are compiled automatically. How I picked up the problem is that I have the following loan constants as an example on which the template is being built. 120,000,000 PV - Total loan 30,000,000 FV - Balloon 8.25% i - Annual Interest 12 n - Monthly payments - Interest calculated at 8.25%/12 1 Jan 00 Start date 31 Dec 09 End date - Baloon payment date + last instalment 10 Term loan - 10 year repayment period 120 NPer - Periods 1 PPMT type - in advance If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? The exercise is actually involved within the shipping industry where Bareboat (Lease) Charters have to be paid monthly in advance. Thanks in advance/sgl |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
Clarification and errata....
I wrote: The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. 1. If you write the terms of the loan with mixed signs, for example: 120,000,000 Lease amount (pv) -30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is negative, and PMT(8.25%/12,120,120000000,-30000000,t) returns a negative number, where t is the payment type (0 for in arrears; 1 for in advance). In that case, I would compute the principal and interest components of the payment as negative numbers, which differs from PPMT and IPMT. And I would maintain the balance due as a negative number. Thus, the formula for the principal and interest paid between periods i and k, inclusive, of n periods is: principal paid: =FV(r,i-1,pmt,pv,t) - FV(r,k,pmt,pv,t) interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,pv,t) + FV(r,k,pmt,pv,t) Note: The signs of the amounts -- positive pv and negative fv, pmt, etc are chosen from the point of view of the borrower. They could be flipped for the lender's point of view. The only requirement is that pv and fv/pmt have opposite signs. I would make the sign of the balance due, principal and interest paid the same as the sign of pmt, which is consistent with the straight-forward use of the FV function. 2. If you write the terms of the loan with unsigned values, which is typical of loan calculators and amortization schedules, for example: 120,000,000 Lease amount (pv) 30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is positive. To make pmt postive, use PMT(8.25%/12,120,-120000000,30000000,t). In that case, I would compute the principal and interest components of the payment as positive numbers, similar to PPMT and IPMT. And I would maintain the balance due as a positive number. Thus, the formula for the principal and interest paid between periods i and k, inclusive, of n periods is: principal paid: =FV(r,i-1,pmt,-pv,t) - FV(r,k,pmt,-pv,t) interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,-pv,t) + FV(r,k,pmt,-pv,t) Note that the only difference between these formulas in #2 versus #1 is: (a) using of -pv instead of pv in all formulas; and (b) reversing FV(i-1) and FV(k) to return consistent signs. The difference between these formulas and my previously posted formulas is the consistent sign of pmt, principal paid and interest paid. ----- original message ----- "JoeU2004" wrote in message ... "sgl" wrote: If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? I tried to explain that before. By "total repayments", you mean the sum of the PPMT results. PPMT is assuming that the first payment applies entirely against principal, whereas standard loan functions (and the mathematics behind them) apply some of the payment toward interest in advance for the period. Alternatively, you can use the formula that I provided in the previous thread, with a tweak, to compute the principal paid down between any two periods, i and k, out of n periods (i <= k <= n). Namely: =fv(r,k,pmt,pv,fv,1) - fv(r,i-1,pmt,pv,fv,1) where r is periodic interest rate corresponding to the payment frequency, and pmt is PMT(r,n,pv,fv,1). This assumes that pv is positive and fv is negative; ergo, pmt is also negative. (In the real world, the PMT function result is rounded to cents, at least. But that will just add to the confusion because in that case, we do not expect things to add up properly anyway.) JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). Yes; and there is nothing wrong with that. The same amount of principal is paid down each period for payment in arrears and in advance. The only difference is the amount of interest paid down and, therefore, the installment payment itself. What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? I do not know what you did wrong, since you neglected to show your formula or method. The amount of interest paid between any two periods, i and k, out of n periods (i <= k <= n), can be computed by: =-pmt*(k - i + 1) - fv(r,k,pmt,pv,fv,1) + fv(r,i-1,pmt,pv,fv,1) That is: the sum of the payments less the principal paid down, expressed as a non-negative number like IPMT. (The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. I prefer to express all numbers as non-negative values and make the necessary adjustments when using the financial functions. Let me know if you would like me to restate everything in non-negative terms.) It might be easier for you to understand all this if you created an amortization schedule. HTH. ----- original message ----- "sgl" wrote in message ... Thank you both (Fred Smith and JoeU2004) for your responses. My apologies for replying so late therefore have lost the thread! What I am trying to compile is an Annuity Template where the user enters the various parameters and all calculations are compiled automatically. How I picked up the problem is that I have the following loan constants as an example on which the template is being built. 120,000,000 PV - Total loan 30,000,000 FV - Balloon 8.25% i - Annual Interest 12 n - Monthly payments - Interest calculated at 8.25%/12 1 Jan 00 Start date 31 Dec 09 End date - Baloon payment date + last instalment 10 Term loan - 10 year repayment period 120 NPer - Periods 1 PPMT type - in advance If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? The exercise is actually involved within the shipping industry where Bareboat (Lease) Charters have to be paid monthly in advance. Thanks in advance/sgl |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
You have been very patient with me and very explicit in your replies and I
thank you for this but I still can't get the interest element to work for me. I will set out the detail of the amortisation schedule below. All input amounts are as per my previous loan constants. The Principal element I have worked out and is listed below In my amortisation the terms of the loan are entered with unsigned values. Period no Pricipal 1 485,123.63 2 488,458.85 3 491,817.01 4 495,198.25 5 498,602.74 6 502,030.63 7 505,482.09 8 508,957.28 9 512,456.36 10 515,979.50 .. .. .. 120 1,096,336.31 ...... and up to period 120 (n). The total equates to 90,000,000 which is what we want. In the interest paid formula that you provided the two FV elements are exactly the same as for the principal calculation except that the signs are reversed. Therefore where I must be going wrong is the pmt*(k-i+1) element. From the above what is k and what is i in say period 5. Similarly what is k and what is i in period 120. For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I may be getting totally confused with the signs which give totally confusing results or looking at numbers for too long!!! Please bear with me. Many thanks in advance/sgl "JoeU2004" wrote: Clarification and errata.... I wrote: The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. 1. If you write the terms of the loan with mixed signs, for example: 120,000,000 Lease amount (pv) -30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is negative, and PMT(8.25%/12,120,120000000,-30000000,t) returns a negative number, where t is the payment type (0 for in arrears; 1 for in advance). In that case, I would compute the principal and interest components of the payment as negative numbers, which differs from PPMT and IPMT. And I would maintain the balance due as a negative number. Thus, the formula for the principal and interest paid between periods i and k, inclusive, of n periods is: principal paid: =FV(r,i-1,pmt,pv,t) - FV(r,k,pmt,pv,t) interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,pv,t) + FV(r,k,pmt,pv,t) Note: The signs of the amounts -- positive pv and negative fv, pmt, etc are chosen from the point of view of the borrower. They could be flipped for the lender's point of view. The only requirement is that pv and fv/pmt have opposite signs. I would make the sign of the balance due, principal and interest paid the same as the sign of pmt, which is consistent with the straight-forward use of the FV function. 2. If you write the terms of the loan with unsigned values, which is typical of loan calculators and amortization schedules, for example: 120,000,000 Lease amount (pv) 30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is positive. To make pmt postive, use PMT(8.25%/12,120,-120000000,30000000,t). In that case, I would compute the principal and interest components of the payment as positive numbers, similar to PPMT and IPMT. And I would maintain the balance due as a positive number. Thus, the formula for the principal and interest paid between periods i and k, inclusive, of n periods is: principal paid: =FV(r,i-1,pmt,-pv,t) - FV(r,k,pmt,-pv,t) interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,-pv,t) + FV(r,k,pmt,-pv,t) Note that the only difference between these formulas in #2 versus #1 is: (a) using of -pv instead of pv in all formulas; and (b) reversing FV(i-1) and FV(k) to return consistent signs. The difference between these formulas and my previously posted formulas is the consistent sign of pmt, principal paid and interest paid. ----- original message ----- "JoeU2004" wrote in message ... "sgl" wrote: If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? I tried to explain that before. By "total repayments", you mean the sum of the PPMT results. PPMT is assuming that the first payment applies entirely against principal, whereas standard loan functions (and the mathematics behind them) apply some of the payment toward interest in advance for the period. Alternatively, you can use the formula that I provided in the previous thread, with a tweak, to compute the principal paid down between any two periods, i and k, out of n periods (i <= k <= n). Namely: =fv(r,k,pmt,pv,fv,1) - fv(r,i-1,pmt,pv,fv,1) where r is periodic interest rate corresponding to the payment frequency, and pmt is PMT(r,n,pv,fv,1). This assumes that pv is positive and fv is negative; ergo, pmt is also negative. (In the real world, the PMT function result is rounded to cents, at least. But that will just add to the confusion because in that case, we do not expect things to add up properly anyway.) JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). Yes; and there is nothing wrong with that. The same amount of principal is paid down each period for payment in arrears and in advance. The only difference is the amount of interest paid down and, therefore, the installment payment itself. What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? I do not know what you did wrong, since you neglected to show your formula or method. The amount of interest paid between any two periods, i and k, out of n periods (i <= k <= n), can be computed by: =-pmt*(k - i + 1) - fv(r,k,pmt,pv,fv,1) + fv(r,i-1,pmt,pv,fv,1) That is: the sum of the payments less the principal paid down, expressed as a non-negative number like IPMT. (The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. I prefer to express all numbers as non-negative values and make the necessary adjustments when using the financial functions. Let me know if you would like me to restate everything in non-negative terms.) It might be easier for you to understand all this if you created an amortization schedule. HTH. ----- original message ----- "sgl" wrote in message ... Thank you both (Fred Smith and JoeU2004) for your responses. My apologies for replying so late therefore have lost the thread! What I am trying to compile is an Annuity Template where the user enters the various parameters and all calculations are compiled automatically. How I picked up the problem is that I have the following loan constants as an example on which the template is being built. 120,000,000 PV - Total loan 30,000,000 FV - Balloon 8.25% i - Annual Interest 12 n - Monthly payments - Interest calculated at 8.25%/12 1 Jan 00 Start date 31 Dec 09 End date - Baloon payment date + last instalment 10 Term loan - 10 year repayment period 120 NPer - Periods 1 PPMT type - in advance If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? The exercise is actually involved within the shipping industry where Bareboat (Lease) Charters have to be paid monthly in advance. Thanks in advance/sgl |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
"sgl" wrote:
I may be getting totally confused Sorry about that. My explanation became unduly abstract. Also, I made an error in my previous posting. I'll try to clarify. In my amortisation the terms of the loan are entered with unsigned values. [....] For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. If all the terms of the loan are unsigned values, I suggested that you compute pmt as an unsigned value as well. My interest formula in part 2 depends on that. That might be contributing to your problem with the interest calculation. Thus, either compute -PMT(...,120000000,-30000000,1), or compute PMT(...,-1200000000,30000000,1). The second form looks at the loan from the lender's point of view, whereas the first form looks at it from the borrower's point of view. Both views are equally valid. Therefore where I must be going wrong is the pmt*(k-i+1) element. Oops: I should have written simply pmt*(k-i). "That might be contributing to your problem" :-). Klunk! From the above what is k and what is i in say period 5. k is 5, the period of interest. i is 4, the previous period number. So pmt*(k-i) is simply pmt. In my original formulation in the other thread, I had used simply k and k-1. That was probably clearer. I generalized to k and i, i <= k, to show that we can compute the sum of payments (principal or interest) directly with the formulas instead of having to sum over a range of payments, as we must do with PPMT and IPMT. I'm afraid that over-generalized added confusion. I still can't get the interest element to work for me. No wonder, considering my mistake. Sorry. The interest paid in a period is simply the full payment less the principal paid in the period. Conversely, the principal paid in a period is the full payment less the interest paid in the period. I presented the "FV - FV" formula to demonstrate how to compute PPMT (corrected) for any specified period or range of periods. Similarly, the "pmt - FV + FV" formula demonstrates how to compute IPMT (corrected) for any specified period or range of periods. But when building an amortization table, you don't need to use those formulas. It is a good idea to use an alternative method in order to validate the formulas. ("He said to himself." ;-) My amortization tables usually have the following columns: payment number, payment amount, principal paid, interest paid, new remaining balance. The first row, before payment number 1, has the loan amount in the "new remaining balance" column. The interest paid is: 1. For payment in arrears: (previousBalance - payment) * (monthlyRate) 2. For payment in advance: previousBalance * monthlyRate The other columns have the same formulas regardless of arrears or in-advance, namely: Principal paid: payment - interestPaid New remaining balance: previousBalance + interestPaid - payment or equivalently: previousBalance - principalPaid I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I will do my best to present this. But my experience has been that the format might get mangled and difficult to read. Sorry if that's the case. Payment Principal Interest Balance 1 1,301,178.03 485,123.63 816,054.40 119,514,876.37 2 1,301,178.03 488,458.85 812,719.18 119,026,417.52 3 1,301,178.03 491,817.01 809,361.02 118,534,600.52 4 1,301,178.03 495,198.25 805,979.78 118,039,402.27 5 1,301,178.03 498,602.74 802,575.29 117,540,799.54 6 1,301,178.03 502,030.63 799,147.40 117,038,768.91 7 1,301,178.03 505,482.09 795,695.94 116,533,286.82 8 1,301,178.03 508,957.28 792,220.75 116,024,329.54 9 1,301,178.03 512,456.36 788,721.67 115,511,873.18 10 1,301,178.03 515,979.50 785,198.53 114,995,893.68 116 1,301,178.03 1,066,698.21 234,479.81 34,340,634.28 117 1,301,178.03 1,074,031.77 227,146.26 33,266,602.51 118 1,301,178.03 1,081,415.73 219,762.29 32,185,186.78 119 1,301,178.03 1,088,850.47 212,327.56 31,096,336.31 120 1,301,178.03 1,096,336.31 204,841.71 30,000,000.00 ----- original message ----- "sgl" wrote in message ... You have been very patient with me and very explicit in your replies and I thank you for this but I still can't get the interest element to work for me. I will set out the detail of the amortisation schedule below. All input amounts are as per my previous loan constants. The Principal element I have worked out and is listed below In my amortisation the terms of the loan are entered with unsigned values. Period no Pricipal 1 485,123.63 2 488,458.85 3 491,817.01 4 495,198.25 5 498,602.74 6 502,030.63 7 505,482.09 8 508,957.28 9 512,456.36 10 515,979.50 . . . 120 1,096,336.31 ..... and up to period 120 (n). The total equates to 90,000,000 which is what we want. In the interest paid formula that you provided the two FV elements are exactly the same as for the principal calculation except that the signs are reversed. Therefore where I must be going wrong is the pmt*(k-i+1) element. From the above what is k and what is i in say period 5. Similarly what is k and what is i in period 120. For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I may be getting totally confused with the signs which give totally confusing results or looking at numbers for too long!!! Please bear with me. Many thanks in advance/sgl "JoeU2004" wrote: Clarification and errata.... I wrote: The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. 1. If you write the terms of the loan with mixed signs, for example: 120,000,000 Lease amount (pv) -30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is negative, and PMT(8.25%/12,120,120000000,-30000000,t) returns a negative number, where t is the payment type (0 for in arrears; 1 for in advance). In that case, I would compute the principal and interest components of the payment as negative numbers, which differs from PPMT and IPMT. And I would maintain the balance due as a negative number. Thus, the formula for the principal and interest paid between periods i and k, inclusive, of n periods is: principal paid: =FV(r,i-1,pmt,pv,t) - FV(r,k,pmt,pv,t) interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,pv,t) + FV(r,k,pmt,pv,t) Note: The signs of the amounts -- positive pv and negative fv, pmt, etc are chosen from the point of view of the borrower. They could be flipped for the lender's point of view. The only requirement is that pv and fv/pmt have opposite signs. I would make the sign of the balance due, principal and interest paid the same as the sign of pmt, which is consistent with the straight-forward use of the FV function. 2. If you write the terms of the loan with unsigned values, which is typical of loan calculators and amortization schedules, for example: 120,000,000 Lease amount (pv) 30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is positive. To make pmt postive, use PMT(8.25%/12,120,-120000000,30000000,t). In that case, I would compute the principal and interest components of the payment as positive numbers, similar to PPMT and IPMT. And I would maintain the balance due as a positive number. Thus, the formula for the principal and interest paid between periods i and k, inclusive, of n periods is: principal paid: =FV(r,i-1,pmt,-pv,t) - FV(r,k,pmt,-pv,t) interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,-pv,t) + FV(r,k,pmt,-pv,t) Note that the only difference between these formulas in #2 versus #1 is: (a) using of -pv instead of pv in all formulas; and (b) reversing FV(i-1) and FV(k) to return consistent signs. The difference between these formulas and my previously posted formulas is the consistent sign of pmt, principal paid and interest paid. ----- original message ----- "JoeU2004" wrote in message ... "sgl" wrote: If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? I tried to explain that before. By "total repayments", you mean the sum of the PPMT results. PPMT is assuming that the first payment applies entirely against principal, whereas standard loan functions (and the mathematics behind them) apply some of the payment toward interest in advance for the period. Alternatively, you can use the formula that I provided in the previous thread, with a tweak, to compute the principal paid down between any two periods, i and k, out of n periods (i <= k <= n). Namely: =fv(r,k,pmt,pv,fv,1) - fv(r,i-1,pmt,pv,fv,1) where r is periodic interest rate corresponding to the payment frequency, and pmt is PMT(r,n,pv,fv,1). This assumes that pv is positive and fv is negative; ergo, pmt is also negative. (In the real world, the PMT function result is rounded to cents, at least. But that will just add to the confusion because in that case, we do not expect things to add up properly anyway.) JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). Yes; and there is nothing wrong with that. The same amount of principal is paid down each period for payment in arrears and in advance. The only difference is the amount of interest paid down and, therefore, the installment payment itself. What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? I do not know what you did wrong, since you neglected to show your formula or method. The amount of interest paid between any two periods, i and k, out of n periods (i <= k <= n), can be computed by: =-pmt*(k - i + 1) - fv(r,k,pmt,pv,fv,1) + fv(r,i-1,pmt,pv,fv,1) That is: the sum of the payments less the principal paid down, expressed as a non-negative number like IPMT. (The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. I prefer to express all numbers as non-negative values and make the necessary adjustments when using the financial functions. Let me know if you would like me to restate everything in non-negative terms.) It might be easier for you to understand all this if you created an amortization schedule. HTH. ----- original message ----- "sgl" wrote in message ... Thank you both (Fred Smith and JoeU2004) for your responses. My apologies for replying so late therefore have lost the thread! What I am trying to compile is an Annuity Template where the user enters the various parameters and all calculations are compiled automatically. How I picked up the problem is that I have the following loan constants as an example on which the template is being built. 120,000,000 PV - Total loan 30,000,000 FV - Balloon 8.25% i - Annual Interest 12 n - Monthly payments - Interest calculated at 8.25%/12 1 Jan 00 Start date 31 Dec 09 End date - Baloon payment date + last instalment 10 Term loan - 10 year repayment period 120 NPer - Periods 1 PPMT type - in advance If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? The exercise is actually involved within the shipping industry where Bareboat (Lease) Charters have to be paid monthly in advance. Thanks in advance/sgl |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
Errata....
I wrote: "sgl" wrote: Therefore where I must be going wrong is the pmt*(k-i+1) element. Oops: I should have written simply pmt*(k-i). "That might be contributing to your problem" :-). Klunk! From the above what is k and what is i in say period 5. k is 5, the period of interest. i is 4, the previous period number. My mistake: I was right the first time. My intent was that for a single period, k and i would be the same, e.g. both 5. We can compute the total interest for the term of the lease by using k=120 and i=1. Thus, for period 5, we have: principal paid: =FV(8.25%/12,5-1,pmt,-120000000,1) - FV(8.25%/12,5,pmt,-120000000,1) interest paid: =pmt*(5-5+1) - FV(8.25%/12,5-1,pmt,-120000000,1) + FV(8.25%/12,5,pmt,-120000000,1) For the entire lease, periods 1 through 120, we have: principal paid: =FV(8.25%/12,1-1,pmt,-120000000,1) - FV(8.25%/12,120,pmt,-120000000,1) interest paid: =pmt*(120-1+1) - FV(8.25%/12,1-1,pmt,-120000000,1) + FV(8.25%/12,120,pmt,-120000000,1) where pmt is the positive montly payment, PMT(8.25%/12,120,-120000000,30000000,1). In the latter case, the computed principal paid should be the same as the original lease amount less the residual. ----- original message ----- "JoeU2004" wrote in message ... "sgl" wrote: I may be getting totally confused Sorry about that. My explanation became unduly abstract. Also, I made an error in my previous posting. I'll try to clarify. In my amortisation the terms of the loan are entered with unsigned values. [....] For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. If all the terms of the loan are unsigned values, I suggested that you compute pmt as an unsigned value as well. My interest formula in part 2 depends on that. That might be contributing to your problem with the interest calculation. Thus, either compute -PMT(...,120000000,-30000000,1), or compute PMT(...,-1200000000,30000000,1). The second form looks at the loan from the lender's point of view, whereas the first form looks at it from the borrower's point of view. Both views are equally valid. Therefore where I must be going wrong is the pmt*(k-i+1) element. Oops: I should have written simply pmt*(k-i). "That might be contributing to your problem" :-). Klunk! From the above what is k and what is i in say period 5. k is 5, the period of interest. i is 4, the previous period number. So pmt*(k-i) is simply pmt. In my original formulation in the other thread, I had used simply k and k-1. That was probably clearer. I generalized to k and i, i <= k, to show that we can compute the sum of payments (principal or interest) directly with the formulas instead of having to sum over a range of payments, as we must do with PPMT and IPMT. I'm afraid that over-generalized added confusion. I still can't get the interest element to work for me. No wonder, considering my mistake. Sorry. The interest paid in a period is simply the full payment less the principal paid in the period. Conversely, the principal paid in a period is the full payment less the interest paid in the period. I presented the "FV - FV" formula to demonstrate how to compute PPMT (corrected) for any specified period or range of periods. Similarly, the "pmt - FV + FV" formula demonstrates how to compute IPMT (corrected) for any specified period or range of periods. But when building an amortization table, you don't need to use those formulas. It is a good idea to use an alternative method in order to validate the formulas. ("He said to himself." ;-) My amortization tables usually have the following columns: payment number, payment amount, principal paid, interest paid, new remaining balance. The first row, before payment number 1, has the loan amount in the "new remaining balance" column. The interest paid is: 1. For payment in arrears: (previousBalance - payment) * (monthlyRate) 2. For payment in advance: previousBalance * monthlyRate The other columns have the same formulas regardless of arrears or in-advance, namely: Principal paid: payment - interestPaid New remaining balance: previousBalance + interestPaid - payment or equivalently: previousBalance - principalPaid I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I will do my best to present this. But my experience has been that the format might get mangled and difficult to read. Sorry if that's the case. Payment Principal Interest Balance 1 1,301,178.03 485,123.63 816,054.40 119,514,876.37 2 1,301,178.03 488,458.85 812,719.18 119,026,417.52 3 1,301,178.03 491,817.01 809,361.02 118,534,600.52 4 1,301,178.03 495,198.25 805,979.78 118,039,402.27 5 1,301,178.03 498,602.74 802,575.29 117,540,799.54 6 1,301,178.03 502,030.63 799,147.40 117,038,768.91 7 1,301,178.03 505,482.09 795,695.94 116,533,286.82 8 1,301,178.03 508,957.28 792,220.75 116,024,329.54 9 1,301,178.03 512,456.36 788,721.67 115,511,873.18 10 1,301,178.03 515,979.50 785,198.53 114,995,893.68 116 1,301,178.03 1,066,698.21 234,479.81 34,340,634.28 117 1,301,178.03 1,074,031.77 227,146.26 33,266,602.51 118 1,301,178.03 1,081,415.73 219,762.29 32,185,186.78 119 1,301,178.03 1,088,850.47 212,327.56 31,096,336.31 120 1,301,178.03 1,096,336.31 204,841.71 30,000,000.00 ----- original message ----- "sgl" wrote in message ... You have been very patient with me and very explicit in your replies and I thank you for this but I still can't get the interest element to work for me. I will set out the detail of the amortisation schedule below. All input amounts are as per my previous loan constants. The Principal element I have worked out and is listed below In my amortisation the terms of the loan are entered with unsigned values. Period no Pricipal 1 485,123.63 2 488,458.85 3 491,817.01 4 495,198.25 5 498,602.74 6 502,030.63 7 505,482.09 8 508,957.28 9 512,456.36 10 515,979.50 . . . 120 1,096,336.31 ..... and up to period 120 (n). The total equates to 90,000,000 which is what we want. In the interest paid formula that you provided the two FV elements are exactly the same as for the principal calculation except that the signs are reversed. Therefore where I must be going wrong is the pmt*(k-i+1) element. From the above what is k and what is i in say period 5. Similarly what is k and what is i in period 120. For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I may be getting totally confused with the signs which give totally confusing results or looking at numbers for too long!!! Please bear with me. Many thanks in advance/sgl "JoeU2004" wrote: Clarification and errata.... I wrote: The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. 1. If you write the terms of the loan with mixed signs, for example: 120,000,000 Lease amount (pv) -30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is negative, and PMT(8.25%/12,120,120000000,-30000000,t) returns a negative number, where t is the payment type (0 for in arrears; 1 for in advance). In that case, I would compute the principal and interest components of the payment as negative numbers, which differs from PPMT and IPMT. And I would maintain the balance due as a negative number. Thus, the formula for the principal and interest paid between periods i and k, inclusive, of n periods is: principal paid: =FV(r,i-1,pmt,pv,t) - FV(r,k,pmt,pv,t) interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,pv,t) + FV(r,k,pmt,pv,t) Note: The signs of the amounts -- positive pv and negative fv, pmt, etc are chosen from the point of view of the borrower. They could be flipped for the lender's point of view. The only requirement is that pv and fv/pmt have opposite signs. I would make the sign of the balance due, principal and interest paid the same as the sign of pmt, which is consistent with the straight-forward use of the FV function. 2. If you write the terms of the loan with unsigned values, which is typical of loan calculators and amortization schedules, for example: 120,000,000 Lease amount (pv) 30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is positive. To make pmt postive, use PMT(8.25%/12,120,-120000000,30000000,t). In that case, I would compute the principal and interest components of the payment as positive numbers, similar to PPMT and IPMT. And I would maintain the balance due as a positive number. Thus, the formula for the principal and interest paid between periods i and k, inclusive, of n periods is: principal paid: =FV(r,i-1,pmt,-pv,t) - FV(r,k,pmt,-pv,t) interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,-pv,t) + FV(r,k,pmt,-pv,t) Note that the only difference between these formulas in #2 versus #1 is: (a) using of -pv instead of pv in all formulas; and (b) reversing FV(i-1) and FV(k) to return consistent signs. The difference between these formulas and my previously posted formulas is the consistent sign of pmt, principal paid and interest paid. ----- original message ----- "JoeU2004" wrote in message ... "sgl" wrote: If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? I tried to explain that before. By "total repayments", you mean the sum of the PPMT results. PPMT is assuming that the first payment applies entirely against principal, whereas standard loan functions (and the mathematics behind them) apply some of the payment toward interest in advance for the period. Alternatively, you can use the formula that I provided in the previous thread, with a tweak, to compute the principal paid down between any two periods, i and k, out of n periods (i <= k <= n). Namely: =fv(r,k,pmt,pv,fv,1) - fv(r,i-1,pmt,pv,fv,1) where r is periodic interest rate corresponding to the payment frequency, and pmt is PMT(r,n,pv,fv,1). This assumes that pv is positive and fv is negative; ergo, pmt is also negative. (In the real world, the PMT function result is rounded to cents, at least. But that will just add to the confusion because in that case, we do not expect things to add up properly anyway.) JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). Yes; and there is nothing wrong with that. The same amount of principal is paid down each period for payment in arrears and in advance. The only difference is the amount of interest paid down and, therefore, the installment payment itself. What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? I do not know what you did wrong, since you neglected to show your formula or method. The amount of interest paid between any two periods, i and k, out of n periods (i <= k <= n), can be computed by: =-pmt*(k - i + 1) - fv(r,k,pmt,pv,fv,1) + fv(r,i-1,pmt,pv,fv,1) That is: the sum of the payments less the principal paid down, expressed as a non-negative number like IPMT. (The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. I prefer to express all numbers as non-negative values and make the necessary adjustments when using the financial functions. Let me know if you would like me to restate everything in non-negative terms.) It might be easier for you to understand all this if you created an amortization schedule. HTH. ----- original message ----- "sgl" wrote in message ... Thank you both (Fred Smith and JoeU2004) for your responses. My apologies for replying so late therefore have lost the thread! What I am trying to compile is an Annuity Template where the user enters the various parameters and all calculations are compiled automatically. How I picked up the problem is that I have the following loan constants as an example on which the template is being built. 120,000,000 PV - Total loan 30,000,000 FV - Balloon 8.25% i - Annual Interest 12 n - Monthly payments - Interest calculated at 8.25%/12 1 Jan 00 Start date 31 Dec 09 End date - Baloon payment date + last instalment 10 Term loan - 10 year repayment period 120 NPer - Periods 1 PPMT type - in advance If you calculate on an arrears basis the total Principal repayments are 90mn over the period (120 months) and the Balloon correctly stands at 30mn whereas if you calculate for payments in advance the total repayments are 90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ??? JoeU2004 your suggestion for the workaround equates to the first intalment of 485,123.63 for principal which is the same result as using the Excel PPMT function in arrears ("0"). What about the interest portion for the first instalment in the event that the payment has to be made in advance? In my exercise this equates to "0"?. Any suggestions on how to work around the interest portion? The exercise is actually involved within the shipping industry where Bareboat (Lease) Charters have to be paid monthly in advance. Thanks in advance/sgl |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
Can't thank you enough for your assistance and your patience and the time you
put into this. Finally got it to work!!! bleary eyed and full of ---/+++ but it works Thanks a million/sgl "JoeU2004" wrote: Errata.... I wrote: "sgl" wrote: Therefore where I must be going wrong is the pmt*(k-i+1) element. Oops: I should have written simply pmt*(k-i). "That might be contributing to your problem" :-). Klunk! From the above what is k and what is i in say period 5. k is 5, the period of interest. i is 4, the previous period number. My mistake: I was right the first time. My intent was that for a single period, k and i would be the same, e.g. both 5. We can compute the total interest for the term of the lease by using k=120 and i=1. Thus, for period 5, we have: principal paid: =FV(8.25%/12,5-1,pmt,-120000000,1) - FV(8.25%/12,5,pmt,-120000000,1) interest paid: =pmt*(5-5+1) - FV(8.25%/12,5-1,pmt,-120000000,1) + FV(8.25%/12,5,pmt,-120000000,1) For the entire lease, periods 1 through 120, we have: principal paid: =FV(8.25%/12,1-1,pmt,-120000000,1) - FV(8.25%/12,120,pmt,-120000000,1) interest paid: =pmt*(120-1+1) - FV(8.25%/12,1-1,pmt,-120000000,1) + FV(8.25%/12,120,pmt,-120000000,1) where pmt is the positive montly payment, PMT(8.25%/12,120,-120000000,30000000,1). In the latter case, the computed principal paid should be the same as the original lease amount less the residual. ----- original message ----- "JoeU2004" wrote in message ... "sgl" wrote: I may be getting totally confused Sorry about that. My explanation became unduly abstract. Also, I made an error in my previous posting. I'll try to clarify. In my amortisation the terms of the loan are entered with unsigned values. [....] For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. If all the terms of the loan are unsigned values, I suggested that you compute pmt as an unsigned value as well. My interest formula in part 2 depends on that. That might be contributing to your problem with the interest calculation. Thus, either compute -PMT(...,120000000,-30000000,1), or compute PMT(...,-1200000000,30000000,1). The second form looks at the loan from the lender's point of view, whereas the first form looks at it from the borrower's point of view. Both views are equally valid. Therefore where I must be going wrong is the pmt*(k-i+1) element. Oops: I should have written simply pmt*(k-i). "That might be contributing to your problem" :-). Klunk! From the above what is k and what is i in say period 5. k is 5, the period of interest. i is 4, the previous period number. So pmt*(k-i) is simply pmt. In my original formulation in the other thread, I had used simply k and k-1. That was probably clearer. I generalized to k and i, i <= k, to show that we can compute the sum of payments (principal or interest) directly with the formulas instead of having to sum over a range of payments, as we must do with PPMT and IPMT. I'm afraid that over-generalized added confusion. I still can't get the interest element to work for me. No wonder, considering my mistake. Sorry. The interest paid in a period is simply the full payment less the principal paid in the period. Conversely, the principal paid in a period is the full payment less the interest paid in the period. I presented the "FV - FV" formula to demonstrate how to compute PPMT (corrected) for any specified period or range of periods. Similarly, the "pmt - FV + FV" formula demonstrates how to compute IPMT (corrected) for any specified period or range of periods. But when building an amortization table, you don't need to use those formulas. It is a good idea to use an alternative method in order to validate the formulas. ("He said to himself." ;-) My amortization tables usually have the following columns: payment number, payment amount, principal paid, interest paid, new remaining balance. The first row, before payment number 1, has the loan amount in the "new remaining balance" column. The interest paid is: 1. For payment in arrears: (previousBalance - payment) * (monthlyRate) 2. For payment in advance: previousBalance * monthlyRate The other columns have the same formulas regardless of arrears or in-advance, namely: Principal paid: payment - interestPaid New remaining balance: previousBalance + interestPaid - payment or equivalently: previousBalance - principalPaid I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I will do my best to present this. But my experience has been that the format might get mangled and difficult to read. Sorry if that's the case. Payment Principal Interest Balance 1 1,301,178.03 485,123.63 816,054.40 119,514,876.37 2 1,301,178.03 488,458.85 812,719.18 119,026,417.52 3 1,301,178.03 491,817.01 809,361.02 118,534,600.52 4 1,301,178.03 495,198.25 805,979.78 118,039,402.27 5 1,301,178.03 498,602.74 802,575.29 117,540,799.54 6 1,301,178.03 502,030.63 799,147.40 117,038,768.91 7 1,301,178.03 505,482.09 795,695.94 116,533,286.82 8 1,301,178.03 508,957.28 792,220.75 116,024,329.54 9 1,301,178.03 512,456.36 788,721.67 115,511,873.18 10 1,301,178.03 515,979.50 785,198.53 114,995,893.68 116 1,301,178.03 1,066,698.21 234,479.81 34,340,634.28 117 1,301,178.03 1,074,031.77 227,146.26 33,266,602.51 118 1,301,178.03 1,081,415.73 219,762.29 32,185,186.78 119 1,301,178.03 1,088,850.47 212,327.56 31,096,336.31 120 1,301,178.03 1,096,336.31 204,841.71 30,000,000.00 ----- original message ----- "sgl" wrote in message ... You have been very patient with me and very explicit in your replies and I thank you for this but I still can't get the interest element to work for me. I will set out the detail of the amortisation schedule below. All input amounts are as per my previous loan constants. The Principal element I have worked out and is listed below In my amortisation the terms of the loan are entered with unsigned values. Period no Pricipal 1 485,123.63 2 488,458.85 3 491,817.01 4 495,198.25 5 498,602.74 6 502,030.63 7 505,482.09 8 508,957.28 9 512,456.36 10 515,979.50 . . . 120 1,096,336.31 ..... and up to period 120 (n). The total equates to 90,000,000 which is what we want. In the interest paid formula that you provided the two FV elements are exactly the same as for the principal calculation except that the signs are reversed. Therefore where I must be going wrong is the pmt*(k-i+1) element. From the above what is k and what is i in say period 5. Similarly what is k and what is i in period 120. For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I may be getting totally confused with the signs which give totally confusing results or looking at numbers for too long!!! Please bear with me. Many thanks in advance/sgl "JoeU2004" wrote: Clarification and errata.... I wrote: The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. 1. If you write the terms of the loan with mixed signs, for example: 120,000,000 Lease amount (pv) -30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is negative, and PMT(8.25%/12,120,120000000,-30000000,t) returns a negative number, where t is the payment type (0 for in arrears; 1 for in advance). In that case, I would compute the principal and interest components of the payment as negative numbers, which differs from PPMT and IPMT. And I would maintain the balance due as a negative number. Thus, the formula for the principal and interest paid between periods i and k, inclusive, of n periods is: principal paid: =FV(r,i-1,pmt,pv,t) - FV(r,k,pmt,pv,t) interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,pv,t) + FV(r,k,pmt,pv,t) Note: The signs of the amounts -- positive pv and negative fv, pmt, etc are chosen from the point of view of the borrower. They could be flipped for the lender's point of view. The only requirement is that pv and fv/pmt have opposite signs. I would make the sign of the balance due, principal and interest paid the same as the sign of pmt, which is consistent with the straight-forward use of the FV function. 2. If you write the terms of the loan with unsigned values, which is typical of loan calculators and amortization schedules, for example: 120,000,000 Lease amount (pv) 30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is positive. To make pmt postive, use PMT(8.25%/12,120,-120000000,30000000,t). In that case, I would compute the principal and interest components of the payment as positive numbers, similar to PPMT and IPMT. And I would maintain |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
Hi. If interested, here is a custom function for cumulative interest.
You can bypass the Pmt() function as it is already incorporated into the function. I left the interest rate at 8.25 so as to make it easier to enter. Function CumInt(Pv, Fv, IntRate, n, s, e) ' n = Number of payments ' s = start ' e = end Dim k, r r = IntRate / 1200 k = 1 + r CumInt = (Fv * (k ^ (e + 1) + r * (-e + s - 1) - k ^ s) + _ Pv * (-k ^ (e + 1) + r * (e - s + 1) * k ^ n + k ^ s)) _ / (k * (k ^ n - 1)) End Function Sub TestIt() 'Period 5 only Debug.Print CumInt(120000000, 30000000, 8.25, 120, 5, 5) 'All periods 1 - 120 Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 120) 'Periods 1 - 10 Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 10) End Sub So, on a worksheet for periods 1-10, you would enter: =CumInt(120000000, 30000000, 8.25, 120, 1, 10) The 3 answers above a 802575.291677935 66141363.223381 8007673.94895974 = = = HTH :) Dana DeLouis sgl wrote: Can't thank you enough for your assistance and your patience and the time you put into this. Finally got it to work!!! bleary eyed and full of ---/+++ but it works Thanks a million/sgl "JoeU2004" wrote: Errata.... I wrote: "sgl" wrote: Therefore where I must be going wrong is the pmt*(k-i+1) element. Oops: I should have written simply pmt*(k-i). "That might be contributing to your problem" :-). Klunk! From the above what is k and what is i in say period 5. k is 5, the period of interest. i is 4, the previous period number. My mistake: I was right the first time. My intent was that for a single period, k and i would be the same, e.g. both 5. We can compute the total interest for the term of the lease by using k=120 and i=1. Thus, for period 5, we have: principal paid: =FV(8.25%/12,5-1,pmt,-120000000,1) - FV(8.25%/12,5,pmt,-120000000,1) interest paid: =pmt*(5-5+1) - FV(8.25%/12,5-1,pmt,-120000000,1) + FV(8.25%/12,5,pmt,-120000000,1) For the entire lease, periods 1 through 120, we have: principal paid: =FV(8.25%/12,1-1,pmt,-120000000,1) - FV(8.25%/12,120,pmt,-120000000,1) interest paid: =pmt*(120-1+1) - FV(8.25%/12,1-1,pmt,-120000000,1) + FV(8.25%/12,120,pmt,-120000000,1) where pmt is the positive montly payment, PMT(8.25%/12,120,-120000000,30000000,1). In the latter case, the computed principal paid should be the same as the original lease amount less the residual. ----- original message ----- "JoeU2004" wrote in message ... "sgl" wrote: I may be getting totally confused Sorry about that. My explanation became unduly abstract. Also, I made an error in my previous posting. I'll try to clarify. In my amortisation the terms of the loan are entered with unsigned values. [....] For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. If all the terms of the loan are unsigned values, I suggested that you compute pmt as an unsigned value as well. My interest formula in part 2 depends on that. That might be contributing to your problem with the interest calculation. Thus, either compute -PMT(...,120000000,-30000000,1), or compute PMT(...,-1200000000,30000000,1). The second form looks at the loan from the lender's point of view, whereas the first form looks at it from the borrower's point of view. Both views are equally valid. Therefore where I must be going wrong is the pmt*(k-i+1) element. Oops: I should have written simply pmt*(k-i). "That might be contributing to your problem" :-). Klunk! From the above what is k and what is i in say period 5. k is 5, the period of interest. i is 4, the previous period number. So pmt*(k-i) is simply pmt. In my original formulation in the other thread, I had used simply k and k-1. That was probably clearer. I generalized to k and i, i <= k, to show that we can compute the sum of payments (principal or interest) directly with the formulas instead of having to sum over a range of payments, as we must do with PPMT and IPMT. I'm afraid that over-generalized added confusion. I still can't get the interest element to work for me. No wonder, considering my mistake. Sorry. The interest paid in a period is simply the full payment less the principal paid in the period. Conversely, the principal paid in a period is the full payment less the interest paid in the period. I presented the "FV - FV" formula to demonstrate how to compute PPMT (corrected) for any specified period or range of periods. Similarly, the "pmt - FV + FV" formula demonstrates how to compute IPMT (corrected) for any specified period or range of periods. But when building an amortization table, you don't need to use those formulas. It is a good idea to use an alternative method in order to validate the formulas. ("He said to himself." ;-) My amortization tables usually have the following columns: payment number, payment amount, principal paid, interest paid, new remaining balance. The first row, before payment number 1, has the loan amount in the "new remaining balance" column. The interest paid is: 1. For payment in arrears: (previousBalance - payment) * (monthlyRate) 2. For payment in advance: previousBalance * monthlyRate The other columns have the same formulas regardless of arrears or in-advance, namely: Principal paid: payment - interestPaid New remaining balance: previousBalance + interestPaid - payment or equivalently: previousBalance - principalPaid I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I will do my best to present this. But my experience has been that the format might get mangled and difficult to read. Sorry if that's the case. Payment Principal Interest Balance 1 1,301,178.03 485,123.63 816,054.40 119,514,876.37 2 1,301,178.03 488,458.85 812,719.18 119,026,417.52 3 1,301,178.03 491,817.01 809,361.02 118,534,600.52 4 1,301,178.03 495,198.25 805,979.78 118,039,402.27 5 1,301,178.03 498,602.74 802,575.29 117,540,799.54 6 1,301,178.03 502,030.63 799,147.40 117,038,768.91 7 1,301,178.03 505,482.09 795,695.94 116,533,286.82 8 1,301,178.03 508,957.28 792,220.75 116,024,329.54 9 1,301,178.03 512,456.36 788,721.67 115,511,873.18 10 1,301,178.03 515,979.50 785,198.53 114,995,893.68 116 1,301,178.03 1,066,698.21 234,479.81 34,340,634.28 117 1,301,178.03 1,074,031.77 227,146.26 33,266,602.51 118 1,301,178.03 1,081,415.73 219,762.29 32,185,186.78 119 1,301,178.03 1,088,850.47 212,327.56 31,096,336.31 120 1,301,178.03 1,096,336.31 204,841.71 30,000,000.00 ----- original message ----- "sgl" wrote in message ... You have been very patient with me and very explicit in your replies and I thank you for this but I still can't get the interest element to work for me. I will set out the detail of the amortisation schedule below. All input amounts are as per my previous loan constants. The Principal element I have worked out and is listed below In my amortisation the terms of the loan are entered with unsigned values. Period no Pricipal 1 485,123.63 2 488,458.85 3 491,817.01 4 495,198.25 5 498,602.74 6 502,030.63 7 505,482.09 8 508,957.28 9 512,456.36 10 515,979.50 . . . 120 1,096,336.31 ..... and up to period 120 (n). The total equates to 90,000,000 which is what we want. In the interest paid formula that you provided the two FV elements are exactly the same as for the principal calculation except that the signs are reversed. Therefore where I must be going wrong is the pmt*(k-i+1) element. From the above what is k and what is i in say period 5. Similarly what is k and what is i in period 120. For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I may be getting totally confused with the signs which give totally confusing results or looking at numbers for too long!!! Please bear with me. Many thanks in advance/sgl "JoeU2004" wrote: Clarification and errata.... I wrote: The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. 1. If you write the terms of the loan with mixed signs, for example: 120,000,000 Lease amount (pv) -30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is negative, and PMT(8.25%/12,120,120000000,-30000000,t) returns a negative number, where t is the payment type (0 for in arrears; 1 for in advance). In that case, I would compute the principal and interest components of the payment as negative numbers, which differs from PPMT and IPMT. And I would maintain the balance due as a negative number. Thus, the formula for the principal and interest paid between periods i and k, inclusive, of n periods is: principal paid: =FV(r,i-1,pmt,pv,t) - FV(r,k,pmt,pv,t) interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,pv,t) + FV(r,k,pmt,pv,t) Note: The signs of the amounts -- positive pv and negative fv, pmt, etc are chosen from the point of view of the borrower. They could be flipped for the lender's point of view. The only requirement is that pv and fv/pmt have opposite signs. I would make the sign of the balance due, principal and interest paid the same as the sign of pmt, which is consistent with the straight-forward use of the FV function. 2. If you write the terms of the loan with unsigned values, which is typical of loan calculators and amortization schedules, for example: 120,000,000 Lease amount (pv) 30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments Note that fv is positive. To make pmt postive, use PMT(8.25%/12,120,-120000000,30000000,t). In that case, I would compute the principal and interest components of the payment as positive numbers, similar to PPMT and IPMT. And I would maintain |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
Dana,
Thank you vm. This works very well. I have a separate column for the period (1,2,3 etc ...120) and therefore declaring the start and end dates in the CumInt column as absolute and relative respectively you get the CumInt to date. In the interest for a specific period I have both start and end dates as relative and therefore I get the interest for that particular period. This is very good formula to have and thank you for your assistance/sgl "Dana DeLouis" wrote: Hi. If interested, here is a custom function for cumulative interest. You can bypass the Pmt() function as it is already incorporated into the function. I left the interest rate at 8.25 so as to make it easier to enter. Function CumInt(Pv, Fv, IntRate, n, s, e) ' n = Number of payments ' s = start ' e = end Dim k, r r = IntRate / 1200 k = 1 + r CumInt = (Fv * (k ^ (e + 1) + r * (-e + s - 1) - k ^ s) + _ Pv * (-k ^ (e + 1) + r * (e - s + 1) * k ^ n + k ^ s)) _ / (k * (k ^ n - 1)) End Function Sub TestIt() 'Period 5 only Debug.Print CumInt(120000000, 30000000, 8.25, 120, 5, 5) 'All periods 1 - 120 Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 120) 'Periods 1 - 10 Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 10) End Sub So, on a worksheet for periods 1-10, you would enter: =CumInt(120000000, 30000000, 8.25, 120, 1, 10) The 3 answers above a 802575.291677935 66141363.223381 8007673.94895974 = = = HTH :) Dana DeLouis sgl wrote: Can't thank you enough for your assistance and your patience and the time you put into this. Finally got it to work!!! bleary eyed and full of ---/+++ but it works Thanks a million/sgl "JoeU2004" wrote: Errata.... I wrote: "sgl" wrote: Therefore where I must be going wrong is the pmt*(k-i+1) element. Oops: I should have written simply pmt*(k-i). "That might be contributing to your problem" :-). Klunk! From the above what is k and what is i in say period 5. k is 5, the period of interest. i is 4, the previous period number. My mistake: I was right the first time. My intent was that for a single period, k and i would be the same, e.g. both 5. We can compute the total interest for the term of the lease by using k=120 and i=1. Thus, for period 5, we have: principal paid: =FV(8.25%/12,5-1,pmt,-120000000,1) - FV(8.25%/12,5,pmt,-120000000,1) interest paid: =pmt*(5-5+1) - FV(8.25%/12,5-1,pmt,-120000000,1) + FV(8.25%/12,5,pmt,-120000000,1) For the entire lease, periods 1 through 120, we have: principal paid: =FV(8.25%/12,1-1,pmt,-120000000,1) - FV(8.25%/12,120,pmt,-120000000,1) interest paid: =pmt*(120-1+1) - FV(8.25%/12,1-1,pmt,-120000000,1) + FV(8.25%/12,120,pmt,-120000000,1) where pmt is the positive montly payment, PMT(8.25%/12,120,-120000000,30000000,1). In the latter case, the computed principal paid should be the same as the original lease amount less the residual. ----- original message ----- "JoeU2004" wrote in message ... "sgl" wrote: I may be getting totally confused Sorry about that. My explanation became unduly abstract. Also, I made an error in my previous posting. I'll try to clarify. In my amortisation the terms of the loan are entered with unsigned values. [....] For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. If all the terms of the loan are unsigned values, I suggested that you compute pmt as an unsigned value as well. My interest formula in part 2 depends on that. That might be contributing to your problem with the interest calculation. Thus, either compute -PMT(...,120000000,-30000000,1), or compute PMT(...,-1200000000,30000000,1). The second form looks at the loan from the lender's point of view, whereas the first form looks at it from the borrower's point of view. Both views are equally valid. Therefore where I must be going wrong is the pmt*(k-i+1) element. Oops: I should have written simply pmt*(k-i). "That might be contributing to your problem" :-). Klunk! From the above what is k and what is i in say period 5. k is 5, the period of interest. i is 4, the previous period number. So pmt*(k-i) is simply pmt. In my original formulation in the other thread, I had used simply k and k-1. That was probably clearer. I generalized to k and i, i <= k, to show that we can compute the sum of payments (principal or interest) directly with the formulas instead of having to sum over a range of payments, as we must do with PPMT and IPMT. I'm afraid that over-generalized added confusion. I still can't get the interest element to work for me. No wonder, considering my mistake. Sorry. The interest paid in a period is simply the full payment less the principal paid in the period. Conversely, the principal paid in a period is the full payment less the interest paid in the period. I presented the "FV - FV" formula to demonstrate how to compute PPMT (corrected) for any specified period or range of periods. Similarly, the "pmt - FV + FV" formula demonstrates how to compute IPMT (corrected) for any specified period or range of periods. But when building an amortization table, you don't need to use those formulas. It is a good idea to use an alternative method in order to validate the formulas. ("He said to himself." ;-) My amortization tables usually have the following columns: payment number, payment amount, principal paid, interest paid, new remaining balance. The first row, before payment number 1, has the loan amount in the "new remaining balance" column. The interest paid is: 1. For payment in arrears: (previousBalance - payment) * (monthlyRate) 2. For payment in advance: previousBalance * monthlyRate The other columns have the same formulas regardless of arrears or in-advance, namely: Principal paid: payment - interestPaid New remaining balance: previousBalance + interestPaid - payment or equivalently: previousBalance - principalPaid I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I will do my best to present this. But my experience has been that the format might get mangled and difficult to read. Sorry if that's the case. Payment Principal Interest Balance 1 1,301,178.03 485,123.63 816,054.40 119,514,876.37 2 1,301,178.03 488,458.85 812,719.18 119,026,417.52 3 1,301,178.03 491,817.01 809,361.02 118,534,600.52 4 1,301,178.03 495,198.25 805,979.78 118,039,402.27 5 1,301,178.03 498,602.74 802,575.29 117,540,799.54 6 1,301,178.03 502,030.63 799,147.40 117,038,768.91 7 1,301,178.03 505,482.09 795,695.94 116,533,286.82 8 1,301,178.03 508,957.28 792,220.75 116,024,329.54 9 1,301,178.03 512,456.36 788,721.67 115,511,873.18 10 1,301,178.03 515,979.50 785,198.53 114,995,893.68 116 1,301,178.03 1,066,698.21 234,479.81 34,340,634.28 117 1,301,178.03 1,074,031.77 227,146.26 33,266,602.51 118 1,301,178.03 1,081,415.73 219,762.29 32,185,186.78 119 1,301,178.03 1,088,850.47 212,327.56 31,096,336.31 120 1,301,178.03 1,096,336.31 204,841.71 30,000,000.00 ----- original message ----- "sgl" wrote in message ... You have been very patient with me and very explicit in your replies and I thank you for this but I still can't get the interest element to work for me. I will set out the detail of the amortisation schedule below. All input amounts are as per my previous loan constants. The Principal element I have worked out and is listed below In my amortisation the terms of the loan are entered with unsigned values. Period no Pricipal 1 485,123.63 2 488,458.85 3 491,817.01 4 495,198.25 5 498,602.74 6 502,030.63 7 505,482.09 8 508,957.28 9 512,456.36 10 515,979.50 . . . 120 1,096,336.31 ..... and up to period 120 (n). The total equates to 90,000,000 which is what we want. In the interest paid formula that you provided the two FV elements are exactly the same as for the principal calculation except that the signs are reversed. Therefore where I must be going wrong is the pmt*(k-i+1) element. From the above what is k and what is i in say period 5. Similarly what is k and what is i in period 120. For "pmt" I am using the Excel PMT function with residual value as a negative "-" i.e - 30,000,000. I would appreciate if you could, from the loan constnts that I have given you, calculate for me the first 10 periods of interest and the last say 5 periods (116 to 120) so that I can grasp where I am going wrong. I may be getting totally confused with the signs which give totally confusing results or looking at numbers for too long!!! Please bear with me. Many thanks in advance/sgl "JoeU2004" wrote: Clarification and errata.... I wrote: The formula is a little perverse because of the mixed signs for pv, fv and pmt; and I hope I got it right. 1. If you write the terms of the loan with mixed signs, for example: 120,000,000 Lease amount (pv) -30,000,000 Residual amount (fv) 8.25% Annual interest rate (monthly rate r = 8.25%/12) 120 Monthly payments |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
Hi. Glad you found it useful.
If interested, here is the change in balance formula between two periods. Function CumPrin(Pv, Fv, IntRate, n, s, e) ' Payment at beginning of period ' n = Number of payments in loan ' s = start ' e = end Dim k, r r = IntRate / 1200 k = 1 + r CumPrin = (Pv - Fv) * (k ^ (e + 1) - k ^ s) / (k * (k ^ n - 1)) CumPrin = Round(CumPrin, 2) End Function The balance change from period 1-10 ? CumPrin(120000000, 30000000, 8.25, 120, 1, 10) 5004106.32 The balance change from 1 - 120 should be the difference pv - fv ? CumPrin(120000000, 30000000, 8.25, 120, 1, 120) 90000000 The Excel formulas for CumIpmt() and CumPrinc() don't have an option for Future value. Unfortunately, they therefore assume a 0 future value. HTH Dana DeLouis = = = = sgl wrote: Dana, Thank you vm. This works very well. I have a separate column for the period (1,2,3 etc ...120) and therefore declaring the start and end dates in the CumInt column as absolute and relative respectively you get the CumInt to date. In the interest for a specific period I have both start and end dates as relative and therefore I get the interest for that particular period. This is very good formula to have and thank you for your assistance/sgl "Dana DeLouis" wrote: Hi. If interested, here is a custom function for cumulative interest. You can bypass the Pmt() function as it is already incorporated into the function. I left the interest rate at 8.25 so as to make it easier to enter. Function CumInt(Pv, Fv, IntRate, n, s, e) ' n = Number of payments ' s = start ' e = end Dim k, r r = IntRate / 1200 k = 1 + r CumInt = (Fv * (k ^ (e + 1) + r * (-e + s - 1) - k ^ s) + _ Pv * (-k ^ (e + 1) + r * (e - s + 1) * k ^ n + k ^ s)) _ / (k * (k ^ n - 1)) End Function Sub TestIt() 'Period 5 only Debug.Print CumInt(120000000, 30000000, 8.25, 120, 5, 5) 'All periods 1 - 120 Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 120) 'Periods 1 - 10 Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 10) End Sub So, on a worksheet for periods 1-10, you would enter: =CumInt(120000000, 30000000, 8.25, 120, 1, 10) The 3 answers above a 802575.291677935 66141363.223381 8007673.94895974 = = = HTH :) Dana DeLouis <snip |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
Dana,
Thank you for your great assistance. This also works verry well. Only slight rounding differences when you put the formula in a worksheet. The total loan repayments amount to 90,000,000.05 leaving a balloon of 29,999,999.95. In the VBE it works fine when you run the macro. sgl "Dana DeLouis" wrote: Hi. Glad you found it useful. If interested, here is the change in balance formula between two periods. Function CumPrin(Pv, Fv, IntRate, n, s, e) ' Payment at beginning of period ' n = Number of payments in loan ' s = start ' e = end Dim k, r r = IntRate / 1200 k = 1 + r CumPrin = (Pv - Fv) * (k ^ (e + 1) - k ^ s) / (k * (k ^ n - 1)) CumPrin = Round(CumPrin, 2) End Function The balance change from period 1-10 ? CumPrin(120000000, 30000000, 8.25, 120, 1, 10) 5004106.32 The balance change from 1 - 120 should be the difference pv - fv ? CumPrin(120000000, 30000000, 8.25, 120, 1, 120) 90000000 The Excel formulas for CumIpmt() and CumPrinc() don't have an option for Future value. Unfortunately, they therefore assume a 0 future value. HTH Dana DeLouis = = = = sgl wrote: Dana, Thank you vm. This works very well. I have a separate column for the period (1,2,3 etc ...120) and therefore declaring the start and end dates in the CumInt column as absolute and relative respectively you get the CumInt to date. In the interest for a specific period I have both start and end dates as relative and therefore I get the interest for that particular period. This is very good formula to have and thank you for your assistance/sgl "Dana DeLouis" wrote: Hi. If interested, here is a custom function for cumulative interest. You can bypass the Pmt() function as it is already incorporated into the function. I left the interest rate at 8.25 so as to make it easier to enter. Function CumInt(Pv, Fv, IntRate, n, s, e) ' n = Number of payments ' s = start ' e = end Dim k, r r = IntRate / 1200 k = 1 + r CumInt = (Fv * (k ^ (e + 1) + r * (-e + s - 1) - k ^ s) + _ Pv * (-k ^ (e + 1) + r * (e - s + 1) * k ^ n + k ^ s)) _ / (k * (k ^ n - 1)) End Function Sub TestIt() 'Period 5 only Debug.Print CumInt(120000000, 30000000, 8.25, 120, 5, 5) 'All periods 1 - 120 Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 120) 'Periods 1 - 10 Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 10) End Sub So, on a worksheet for periods 1-10, you would enter: =CumInt(120000000, 30000000, 8.25, 120, 1, 10) The 3 answers above a 802575.291677935 66141363.223381 8007673.94895974 = = = HTH :) Dana DeLouis <snip |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PPMT Function Repost
"sgl" wrote:
Thank you for your great assistance. This also works verry well. Only slight rounding differences when you put the formula in a worksheet. The total loan repayments amount to 90,000,000.05 leaving a balloon of 29,999,999.95. In the VBE it works fine when you run the macro. I cannot imagine why anyone would resort to such a complex solution, especially a UDF, when a straight-forward Excel solution exists. (Not to mention an Excel solution that works for in-arrears as well as in-advance payments with one obvious, well-documented change to a function argument.) But FYI, I do not get any such numerical error, whether I use the UDF as Dana wrote it, or I implement it in an Excel formula. I am using Excel 2003. Nonetheless, I would not be surprised by a 0.01 difference between an Excel and VB implementation with some loan parameters. The VB Round function behaves differently from the Excel ROUND function; specifically, the VB Round function does "banker's rounding". Unless that is what you want (!), I would suggest the following change to Dana's UDF: CumPrin = WorksheetFunction.Round(CumPrin, 2) ----- original message ----- "sgl" wrote in message ... Dana, Thank you for your great assistance. This also works verry well. Only slight rounding differences when you put the formula in a worksheet. The total loan repayments amount to 90,000,000.05 leaving a balloon of 29,999,999.95. In the VBE it works fine when you run the macro. sgl "Dana DeLouis" wrote: Hi. Glad you found it useful. If interested, here is the change in balance formula between two periods. Function CumPrin(Pv, Fv, IntRate, n, s, e) ' Payment at beginning of period ' n = Number of payments in loan ' s = start ' e = end Dim k, r r = IntRate / 1200 k = 1 + r CumPrin = (Pv - Fv) * (k ^ (e + 1) - k ^ s) / (k * (k ^ n - 1)) CumPrin = Round(CumPrin, 2) End Function The balance change from period 1-10 ? CumPrin(120000000, 30000000, 8.25, 120, 1, 10) 5004106.32 The balance change from 1 - 120 should be the difference pv - fv ? CumPrin(120000000, 30000000, 8.25, 120, 1, 120) 90000000 The Excel formulas for CumIpmt() and CumPrinc() don't have an option for Future value. Unfortunately, they therefore assume a 0 future value. HTH Dana DeLouis = = = = sgl wrote: Dana, Thank you vm. This works very well. I have a separate column for the period (1,2,3 etc ...120) and therefore declaring the start and end dates in the CumInt column as absolute and relative respectively you get the CumInt to date. In the interest for a specific period I have both start and end dates as relative and therefore I get the interest for that particular period. This is very good formula to have and thank you for your assistance/sgl "Dana DeLouis" wrote: Hi. If interested, here is a custom function for cumulative interest. You can bypass the Pmt() function as it is already incorporated into the function. I left the interest rate at 8.25 so as to make it easier to enter. Function CumInt(Pv, Fv, IntRate, n, s, e) ' n = Number of payments ' s = start ' e = end Dim k, r r = IntRate / 1200 k = 1 + r CumInt = (Fv * (k ^ (e + 1) + r * (-e + s - 1) - k ^ s) + _ Pv * (-k ^ (e + 1) + r * (e - s + 1) * k ^ n + k ^ s)) _ / (k * (k ^ n - 1)) End Function Sub TestIt() 'Period 5 only Debug.Print CumInt(120000000, 30000000, 8.25, 120, 5, 5) 'All periods 1 - 120 Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 120) 'Periods 1 - 10 Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 10) End Sub So, on a worksheet for periods 1-10, you would enter: =CumInt(120000000, 30000000, 8.25, 120, 1, 10) The 3 answers above a 802575.291677935 66141363.223381 8007673.94895974 = = = HTH :) Dana DeLouis <snip |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| PPMT Function | Excel Worksheet Functions | |||
| PPMT function replacement | Excel Worksheet Functions | |||
| Repost of function problem | Excel Worksheet Functions | |||
| REPOST: Which Function to Use | Excel Worksheet Functions | |||
| PPMT Function with varies interest rate | Excel Worksheet Functions | |||
Linear Mode
