Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate total interest earned on a loan and monthly payment Matt Stanley Excel Worksheet Functions 4 May 4th 23 03:42 AM
how calculate a loan amount with quarterly compounded interest Willy Verhaegen - Belgium Excel Discussion (Misc queries) 1 June 6th 08 10:26 AM
how to calculate average loan period of a 5 year loan Jayant Excel Worksheet Functions 2 March 26th 08 12:47 AM
Calculate correct interest on loan when debtor pays late O&D Excel Discussion (Misc queries) 0 May 3rd 05 05:10 PM
How to calculate total interest on 12 month loan with early payments Fred Smith Excel Worksheet Functions 0 January 6th 05 03:33 AM


All times are GMT +1. The time now is 08:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"