Function to generate Interest Only? pmt()

[jeffw85]

Is there a way to generate an interest only payment in a calculated field? I have no problem with the regular payment function but I do not see any functions that will calculate the IO payment

Any ideas greatly appreciated!

[Stan Mathews]

Re: Function to generate Interest Only? pmt()

Is there a way to generate an interest only payment in a calculated field? I have no problem with the regular payment function but I do not see any functions that will calculate the IO payment

Any ideas greatly appreciated!

Do you mean the payment on an interest only loan or the interest portion of a regular loan?

I don't think you can have an expression for the interest portion of a regular loan. Everything I see indicates you have to look that up in a schedule (table).

[jeffw85]

Re: Function to generate Interest Only? pmt()

Stan

In my case I believe the result would be the same either way. But in actuality I would prefer to calculate for an interest only loan. All I am trying to accomplish is the first interest only payment. eg. a 100k loan at 6% results in 599.55 P & I of which 500 even represents the interest only portion of that payment.

So in essence--I am wishing to figure the initial interest payment only.

Hope that helps

[G Gabriel]

Re: Function to generate Interest Only? pmt()

Jeff:

All I am trying to accomplish is the first interest only payment.

If that's all you want, then it's a very simple matter:
Loan_Amount*Interest_Rate/12

[jeffw85]

Re: Function to generate Interest Only? pmt()

Stan

I got it from the web link you posted--I simply will enter the calculation manually without trying to use a function. I should have known how to do this anyway--Call it a rock attack!!! Blah!

Thanks again

[CALocklin]

Re: Function to generate Interest Only? pmt()

If that's all you want, then it's a very simple matter:
Loan_Amount*Interest_Rate/12

If it's an APR, then I believe you would have to modify that a bit to handle the issue of compounding interest for 12 months. ("reverse compounding" since we are making payments rather than getting paid???)

If it's a mortgage, the closing fees and other expenses should be included in the overall calculation which adds to the complexity. However, if these are ignored, the calculation becomes much simpler. Here's what I found on the internet:

For credit cards the APR is a much simpler calculation. Due to the fact that the amount of money borrowed really isn�t known, you can not use the formula that is used for most loans. It�s simply a calculation of what the effective interest rate is for one year when you take into account that the interest is compounded monthly.

The formula for this is apr=(interest/12 + 1)^12. So for a card with a 10% interest rate it would be apr=(0.1%/12)^12, which is apr=1.0083^12, so apr=1.104 or approximated 11%.

I believe this is correct other than the fact that 1.104 is really 10.4% and not 11%. BUT, notice that the APR is 10.4% while the original calculation used 10%. The trick is to go the other way. Knowing the APR, what is the monthly %? Note that the monthly % will be lower than just the APR/12.

HOWEVER, since it's an interest only situation, I'm not sure compounding would be considered. I think it would but I'm not sure. Are there any experts out there who can confirm this one way or the other? (There must be at least one accountant type out there using A5 who knows the definitive answer.)

Here's my logic to why it would be considered: If I borrow $1200 for 1 year at a rate of 10% per year, the interest payment at the end of the year (assuming only one payment per year) would be $120. Simple and straight forward. However, if I make monthly payments, the total paid should be slightly less because the recipient of the money has the use of my first payment for 11 of those 12 months. In other words, the recipient has the opportunity to make interest on my interest. Therefore, my interest payments should be slightly less than the 10%/12 even though the APR is 10%.

If this is correct, then the payment calculation would be (1+interest_rate)^(1/12) and with an APR rate of 10%, it would be 1.1^(1/12)=1.00797414 or about 0.7974% per month. The total paid would be 1200*.00797414*12 or $114.83 BUT since the recipient was able to use (and presumably invest) some of that money longer, his "value" for the year would have been $120.

[G Gabriel]

Re: Function to generate Interest Only? pmt()

Cal:
I hear you and my first impression was that he was asking about how to calculate the interest portion of each monthly payment of a mortgage loan (alpha does not have such function, I created my own). Then I saw the question re-phrased and asking for the FIRST month's interest only!

[Raymond Lyons]

Re: Function to generate Interest Only? pmt()

If it's an APR, then I believe you would have to modify that a bit to handle the issue of compounding interest for 12 months. ("reverse compounding" since we are making payments rather than getting paid???)

If it's a mortgage, the closing fees and other expenses should be included in the overall calculation which adds to the complexity. However, if these are ignored, the calculation becomes much simpler. Here's what I found on the internet:I believe this is correct other than the fact that 1.104 is really 10.4% and not 11%. BUT, notice that the APR is 10.4% while the original calculation used 10%. The trick is to go the other way. Knowing the APR, what is the monthly %? Note that the monthly % will be lower than just the APR/12.

HOWEVER, since it's an interest only situation, I'm not sure compounding would be considered. I think it would but I'm not sure. Are there any experts out there who can confirm this one way or the other? (There must be at least one accountant type out there using A5 who knows the definitive answer.)

Here's my logic to why it would be considered: If I borrow $1200 for 1 year at a rate of 10% per year, the interest payment at the end of the year (assuming only one payment per year) would be $120. Simple and straight forward. However, if I make monthly payments, the total paid should be slightly less because the recipient of the money has the use of my first payment for 11 of those 12 months. In other words, the recipient has the opportunity to make interest on my interest. Therefore, my interest payments should be slightly less than the 10%/12 even though the APR is 10%.

If this is correct, then the payment calculation would be (1+interest_rate)^(1/12) and with an APR rate of 10%, it would be 1.1^(1/12)=1.00797414 or about 0.7974% per month. The total paid would be 1200*.00797414*12 or $114.83 BUT since the recipient was able to use (and presumably invest) some of that money longer, his "value" for the year would have been $120.

Cal,

I am no expert, but first, given Jeff's original question Gabriel's answer is correct i.e., AMOUNT*((RATE/100)/12) [or just rate/12 depending on how the rate is expressed].

Second, I think both you and the web site you referenced are confusing APR (annual percentage rate) with APY (annual percentage yield). For a credit card the APR is not something anyone needs to calculate whereas the APY is, using the formula: (1 + monthly rate)^12-1 = APY or (1 + 0.00833333)^12-1 = 0.104713 for a 10% APR. (see http://www.investopedia.com/articles.../04/102904.asp for a discussion.)

As for the APR for a mortgage, there is no "formula" for this. In the old days one had to get out thick books of tables to find the APR. Nowadays one gets out a handy HP financial calculator, which of course is a computer, and you'll notice that when you have it do an APR calculation it will whirl away for a rather long bit before it arrives at an answer, probably looping through a whole bunch possible answers until it says enough is enough and displays one that is close enough for government work. Years ago, when I was quite a bit dumber, I tried to do an xbasic function to replicate the results produced by HP. It more or less worked but I am sure better brains could improve it immensely--except that I would be embarrassed to share what I did (I did not really know what I was doing).

About that "close enough for government work" statement. Part of this is that there is no absolute, down to the last decimal point answer--just closer and closer approximations until someone says "that's close enough." However, the bigger part of the problem, at least in the mortgage business, is that there is no hard and fast rule or law that says this or that fee must be included in the costs that must be added to the amount borrowed when determining the APR. Various lenders and brokers can and do fudge things here and there, which undermines the intent of the law in this area (which is to allow a prospective borrower to fairly compare various loan options).

But like I said, I do not pretend to be an expert on all this, so take the above for what it may (or may not!) be worth. I would love to be corrected/educated.

Ray Lyons

[DaveM]

Re: Function to generate Interest Only? pmt()

Amazing that this came to view. I have to do an amortization schedule which includes payment, interest, principle and actual monthly rate for any number of months. To compund the problem, many loans have a 45 days to first or 15 days to first or............ These days have to be figured like 45 would wind up 360.5 months as the loan goes, but made in 360 equal monthly payments.

I have to comply with regulation Z for which I have a book(written in Greek?).

Thanks Gabe, I think I got it.