Hello,
I am using the IPMT formula to calculate 5% monthly interest on an investment based on a start date (when the investment was made) and an end date. However, when I change the amount of months, the interest is accrued the interest stays the same (highlighted in yellow on the attached spreadsheet).
Does anyone have any ideas what I am doing wrong here?
Any help would be greatly appreciated!!
Try: Ribbon => Formulas => Calculations Option => Automatic
I've changed amount and every time I see a new value of accrued interest
ipmt_opt.gif
Last edited by sandy666; 05-05-2017 at 06:58 AM.
Hi Adam,
I think you want to use the future value (fv) function instead of ipmt. If you look at your formula [IPMT(-C6,1,D6,A6)], your accrued interest evaluated is for "1", which means your result will remain static no matter what you change your NPER value to.
The following comes with the assumption that your interest is 5% per year, compounded monthly.
I notice you have a Issue Date and Close Date, so if you're just looking at the interest accrued at closing, you can use this:
=FV(C5/12,DATEDIF(B5,$B$2,"m"),0,-A5)-A5
You can also put the DATEDIF formula in D5 and your FV would be cleaner:
=FV(C5/12,D5,0,-A5)-A5
You can also use the above formula if you're trying to find the interest accrued at any particular month during the investment. Just manually enter any month in D5.
Note: You don't need to know your start or close date unless my previous assumption was incorrect and the interest is 5% over the entire period.
@adam_d_john, I believe that is the answer you are looking for.Originally Posted by sandy666
However, we might also note that your use of IPMT is "unusual"; in fact, I'm surprised it works.
You write: =IPMT(-C5,1,D5,A5). -C5 is a negative(!) interest rate. Normally, the interest rate is positive.
Instead, what should be signed are the cash flows: PV, FV and payments. For example, positive for inflows and negative for outflows; or vice versa. So you might write:
=-IPMT(C5,1,D5,A5)
or
=IPMT(C5,1,D5,-A5)
if you want IPMT to return a positive number.
Also, are you sure that 5% is the monthly(!) interest rate?
That would be annual rate of 60%(!) or about 78.59%(!), depending on regional practices.
Usually, interest rate is stated as an annual rate.
In the US and many other countries, the annual rate is stated as a simple rate. So the monthly rate would be C5/12.
In the EU and some other countries, the annual rate is stated (sometimes, depending on the country) as a compounded rate. So the monthly would be (1+C5)^(1/12)-1.
(And don't even get me started to comment on Canada. :->)
Last edited by joeu2004; 05-05-2017 at 07:11 PM.
Hi all:
Many thanks for your responses. @joe2004 - you are quite right - I meant the interest rate to be annual.
I appreciate all your help in enabling me to solve this.
All the best!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks