Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you calculate an interest only loan
I want to set up a loan, lets say for 100,000. The payments are interest
only, payable and compounded monthly. the principal of the loan is due on 60 months. what if i wanted to then amortorize the principal at the end of the 5 year period for another 3 years after the interest only term. The interest is compounded daily and payable over 36 months -- thank you so much! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you calculate an interest only loan
"Richard" wrote:
Subject:: How do you calculate an interest only loan I want to set up a loan, lets say for 100,000. The payments are interest only, payable and compounded monthly. the principal of the loan is due on 60 months. The answer might depend on the your country. The following applies to the US, according to Reg Z ("Truth in Lending"). (Note: This applies to a regulated lender. For a private loan, anything is possible.) If the annual interest rate is 5%, the monthly rate is 5%/12, and the interest-only monthly payment is 100000*5%/12. what if i wanted to then amortorize the principal at the end of the 5 year period for another 3 years after the interest only term. The interest is compounded daily and payable over 36 months This is a little trickier because of the requirement to compound interest daily. The "principal at the end of the [interest-only] 5-year period" is the original amount of the loan, of course. If the annual interest rate is still 5%, the daily rate is 5%/365 (even in a leap year). Since the interest is compounded daily, the amount of interest paid each month is typically (payment in arrears): previousBalance*(1+5%/365)^(d2-d1) - previousBalance where d1 and d2 are the previous and current payment due dates. Typically, d1 and d2 are the same of day of each month. If the first payment is due on the 31st, the last day of each month is used. If the first payment is due on the 29th or 30th, the last day of Feb is used. The amount of principal paid is: payment - interestPaid The new balance is: previousBalance + interestPaid - payment; or equivalently: previousBalance - principalPaid. There is no regulation for how the monthly payment is determined when the interest is compounded daily and paid monthly. I would determine regular monthly payment this way: ROUND(PMT((1+5%/365)^(365/12)-1,12,-100000),2) But there are other reasonable ways to formulate that. Also note that the amount of last payment might be different due to real-world rounding and the variable amount of interest each month. So I would compute the last payment this way: MIN(regularPayment, ROUND(previousBalance*(1+5%/365)^(d2-d1),2)) (You can use the same formula for all periodic payments, if you prefer.) ----- original message ----- "Richard" wrote in message ... I want to set up a loan, lets say for 100,000. The payments are interest only, payable and compounded monthly. the principal of the loan is due on 60 months. what if i wanted to then amortorize the principal at the end of the 5 year period for another 3 years after the interest only term. The interest is compounded daily and payable over 36 months -- thank you so much! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you calculate an interest only loan
Errata....
I wrote: I would determine regular monthly payment this way: ROUND(PMT((1+5%/365)^(365/12)-1,12,-100000),2) I meant: ROUND(PMT((1+5%/365)^(365/12)-1,3*12,-100000),2) ----- original message ----- "JoeU2004" wrote in message ... "Richard" wrote: Subject:: How do you calculate an interest only loan I want to set up a loan, lets say for 100,000. The payments are interest only, payable and compounded monthly. the principal of the loan is due on 60 months. The answer might depend on the your country. The following applies to the US, according to Reg Z ("Truth in Lending"). (Note: This applies to a regulated lender. For a private loan, anything is possible.) If the annual interest rate is 5%, the monthly rate is 5%/12, and the interest-only monthly payment is 100000*5%/12. what if i wanted to then amortorize the principal at the end of the 5 year period for another 3 years after the interest only term. The interest is compounded daily and payable over 36 months This is a little trickier because of the requirement to compound interest daily. The "principal at the end of the [interest-only] 5-year period" is the original amount of the loan, of course. If the annual interest rate is still 5%, the daily rate is 5%/365 (even in a leap year). Since the interest is compounded daily, the amount of interest paid each month is typically (payment in arrears): previousBalance*(1+5%/365)^(d2-d1) - previousBalance where d1 and d2 are the previous and current payment due dates. Typically, d1 and d2 are the same of day of each month. If the first payment is due on the 31st, the last day of each month is used. If the first payment is due on the 29th or 30th, the last day of Feb is used. The amount of principal paid is: payment - interestPaid The new balance is: previousBalance + interestPaid - payment; or equivalently: previousBalance - principalPaid. There is no regulation for how the monthly payment is determined when the interest is compounded daily and paid monthly. I would determine regular monthly payment this way: ROUND(PMT((1+5%/365)^(365/12)-1,12,-100000),2) But there are other reasonable ways to formulate that. Also note that the amount of last payment might be different due to real-world rounding and the variable amount of interest each month. So I would compute the last payment this way: MIN(regularPayment, ROUND(previousBalance*(1+5%/365)^(d2-d1),2)) (You can use the same formula for all periodic payments, if you prefer.) ----- original message ----- "Richard" wrote in message ... I want to set up a loan, lets say for 100,000. The payments are interest only, payable and compounded monthly. the principal of the loan is due on 60 months. what if i wanted to then amortorize the principal at the end of the 5 year period for another 3 years after the interest only term. The interest is compounded daily and payable over 36 months -- thank you so much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate total interest earned on a loan and monthly payment | Excel Worksheet Functions | |||
how calculate a loan amount with quarterly compounded interest | Excel Discussion (Misc queries) | |||
how to calculate average loan period of a 5 year loan | Excel Worksheet Functions | |||
Calculate correct interest on loan when debtor pays late | Excel Discussion (Misc queries) | |||
How to calculate total interest on 12 month loan with early payments | Excel Worksheet Functions |