Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PMT formula help
I need a function(formula) that will amortize a loan based on a 365/360
(actual/360) basis, no compounding, with payment due at the end of each payment period (ie monthly payments with note dated 11/6, first payment 12/6) So with a loan amount of $1,662,000 at 7.5% 10year amortization the loan payment should be $19,821.32 with this formula (On our current formula we come up with $19,728.24) This is what I am currently using but it is not a true 30/360 formula =ROUND(-(PMT(C11/12,C12,C10)-0.005),2) where C11= Annual IR, C12= Term in months and C10=Original loan amount and is not accurate for what I need. Can anyone help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
PMT formula help
<payment should be $19,821.32
How did you get that? I'm not sure the 365/360 matters. If you do not pay monthly but every period (however defined), than the formula is correct, as you can easily check by setting up an amortization table. But maybe you want to tweak the interest%. Question is How? -- Kind regards, Niek Otten Microsoft MVP - Excel "nicoleradke" wrote in message ... |I need a function(formula) that will amortize a loan based on a 365/360 | (actual/360) basis, no compounding, with payment due at the end of each | payment period (ie monthly payments with note dated 11/6, first payment 12/6) | So with a loan amount of $1,662,000 at 7.5% 10year amortization the loan | payment should be $19,821.32 with this formula (On our current formula we | come up with $19,728.24) | | This is what I am currently using but it is not a true 30/360 formula | =ROUND(-(PMT(C11/12,C12,C10)-0.005),2) where C11= Annual IR, C12= Term in | months and C10=Original loan amount and is not accurate for what I need. Can | anyone help? | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
PMT formula help
Niek Otten wrote:
I'm not sure the 365/360 matters. [....] But maybe you want to tweak the interest%. Question is How? Perhaps this pointer will help to put the discussion on track: http://www.efanniemae.com/mf/guidesf...os/LM01_15.doc . Unfortunately, the document is not self-explanatory, IMHO. But this statement might be helpful: "Calculation of the monthly principal and interest (P&I) payment for a loan utilizing the Actual/360 option is the same as for a loan using the standard 30/360 option. The difference between the Actual/360 and 30/360 methods is that the amount of each monthly payment that is allocated to interest will be based on the actual number of calendar days during such month. In a 31-day month, more of the monthly payment amount will be allocated toward interest than toward principal under the Actual/360 method as compared to 30/360 method. The amount allocated to interest for each month will vary depending on the actual number of calendar days during such month. As a result, since there are actually 365/366 days in a year, loans using an Actual/360 payment schedule amortize more slowly and generate more interest than a loan at the same note rate using a 30/360 payment." |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
PMT formula help
"nicoleradke" wrote:
I need a function(formula) that will amortize a loan based on a 365/360 (actual/360) basis, no compounding, with payment due at the end of each payment period (ie monthly payments with note dated 11/6, first payment 12/6) So with a loan amount of $1,662,000 at 7.5% 10year amortization the loan payment should be $19,821.32 with this formula Sorry, but I cannot provide a formula (yet). In fact, I suspect that no formula will suffice, unless you write a VBA function. (Note: Perhaps Excel's goal-seek feature would do the trick. I am not sufficiently familiar with it to try.) But I can confirm that $19,821.32 is the smallest payment that reduces the loan to zero based on the above terms and my interpretation of the actual/360 method described in http://www.efanniemae.com/mf/guidesf...os/LM01_15.doc . (On our current formula we come up with $19,728.24) This is what I am currently using but it is not a true 30/360 formula =ROUND(-(PMT(C11/12,C12,C10)-0.005),2) I would compute that more simply as follows: =roundup(pmt(C11/12, C12, -C10), 2) where C11 is 7.5%, C12 is 10*12, and C10 is 1,662,000. It is debatable whether to use ROUNDUP() or ROUND(). But in either case, that payment works if we assume that the interest per month is computed using the constant monthly interest rate of 7.5%/12. (I presume that is the so-called 30/360 method.) But I presume that the actual/360 method computes the interest per month as (D2-D1)*7.5%/360, where D1 and D2 are the previous and current payment dates respectively. Indeed, when I set up a 120-month amortization schedule based on that assumption -- using 11/6/2006 as the loan date, 12/6/2006 as the first payment date, and the 6th of the month for each successive payment -- the loan is reduced to zero, with the last payment amounting to $19,821.06. If you are interested, I could explain a paradigm for setting up the amortization schedule and doing a "goal seek" algorithm manually. The algorithm could be implemented using VBA -- but someone else would have to provide that solution. HTH. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
PMT formula help
Thanks, I will tweak it some more. I have a complete amortization schedule
setup. " wrote: "nicoleradke" wrote: I need a function(formula) that will amortize a loan based on a 365/360 (actual/360) basis, no compounding, with payment due at the end of each payment period (ie monthly payments with note dated 11/6, first payment 12/6) So with a loan amount of $1,662,000 at 7.5% 10year amortization the loan payment should be $19,821.32 with this formula Sorry, but I cannot provide a formula (yet). In fact, I suspect that no formula will suffice, unless you write a VBA function. (Note: Perhaps Excel's goal-seek feature would do the trick. I am not sufficiently familiar with it to try.) But I can confirm that $19,821.32 is the smallest payment that reduces the loan to zero based on the above terms and my interpretation of the actual/360 method described in http://www.efanniemae.com/mf/guidesf...os/LM01_15.doc . (On our current formula we come up with $19,728.24) This is what I am currently using but it is not a true 30/360 formula =ROUND(-(PMT(C11/12,C12,C10)-0.005),2) I would compute that more simply as follows: =roundup(pmt(C11/12, C12, -C10), 2) where C11 is 7.5%, C12 is 10*12, and C10 is 1,662,000. It is debatable whether to use ROUNDUP() or ROUND(). But in either case, that payment works if we assume that the interest per month is computed using the constant monthly interest rate of 7.5%/12. (I presume that is the so-called 30/360 method.) But I presume that the actual/360 method computes the interest per month as (D2-D1)*7.5%/360, where D1 and D2 are the previous and current payment dates respectively. Indeed, when I set up a 120-month amortization schedule based on that assumption -- using 11/6/2006 as the loan date, 12/6/2006 as the first payment date, and the 6th of the month for each successive payment -- the loan is reduced to zero, with the last payment amounting to $19,821.06. If you are interested, I could explain a paradigm for setting up the amortization schedule and doing a "goal seek" algorithm manually. The algorithm could be implemented using VBA -- but someone else would have to provide that solution. HTH. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
PMT formula help
It matters in the fact that when you get to larger loan amount the monthly
repayment can have as much as a hundred dollar difference in payment. You can not just adjust the interest rate. But it does work when you compute the interest compounding daily payable at the end of each month. Thanks for your input. "Niek Otten" wrote: <payment should be $19,821.32 How did you get that? I'm not sure the 365/360 matters. If you do not pay monthly but every period (however defined), than the formula is correct, as you can easily check by setting up an amortization table. But maybe you want to tweak the interest%. Question is How? -- Kind regards, Niek Otten Microsoft MVP - Excel "nicoleradke" wrote in message ... |I need a function(formula) that will amortize a loan based on a 365/360 | (actual/360) basis, no compounding, with payment due at the end of each | payment period (ie monthly payments with note dated 11/6, first payment 12/6) | So with a loan amount of $1,662,000 at 7.5% 10year amortization the loan | payment should be $19,821.32 with this formula (On our current formula we | come up with $19,728.24) | | This is what I am currently using but it is not a true 30/360 formula | =ROUND(-(PMT(C11/12,C12,C10)-0.005),2) where C11= Annual IR, C12= Term in | months and C10=Original loan amount and is not accurate for what I need. Can | anyone help? | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |