Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
Hi,
I'm having trouble with an Excel financial function mainly because I don't really understand the financial jargon that is being used to explain it. I want to check that a) I am using the right function and then b) that I'm using it correctly. I'll try to explain the situation in layman's terms : My friend, Mr X, placed some money in a current account in the bank where his money gains no interest. I told him he'd be better off using a savings account where - even if the interest is small - he would make a serious benefit in the long term because the amount in there is quite important and it stays in over a long period. So now I'm trying to prove this to him using Excel. The interest is calculated from day to day. So I'm using the ACCRINTM function calculating (or so I hope) the interest accrued between two movements. Let's say the savings account pays an annual rate of 3.5% and is 1. Opened on Jan 1st with 1000 Euros. 2. On Jan 15 , I pay a bill for 500 Euros. 3. On Jan 30th, I receive a cheque for 400 Euros. I'm recording each movement on a row by row basis, using for "issue" the date of the movement, using for "settlement" the date of the next movement, using for "par" the balance remaining in the account between the two movements, and on the last row, for the "settlement" I use todays date. ACCRINTM ( Date(2008; 01;01) ; Date(2008; 01;15) ; 3.5% ; 1000 ; 4 ) ACCRINTM ( Date(2008; 01;15) ; Date(2008; 01;30) ; 3.5% ; 500 ; 4 ) ACCRINTM ( Date(2008; 01;30) ; Date(... Now ) ; 3.5% ; 900 ; 4 ) Then I sum all the results and I tell Mr X that that's the amount he lost! But am I right? TIA Nth |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
No, you're not.
ACCRINTM is used to calculate the accrued interest owing when you purchase a bond. Suppose you buy a bond today that last paid interest on June 15th. You will receive the full semi-annual payment on December 15th, but you're not entitled to this full amount, because you didn't own the bond for the entire period.. Therefore, you owe the seller the interest earned between June 15th and now (the "accrued interest"). In your formulas, rather than calculating 15 days interest, Accrintm is calculating 168 days interest. To do what you want, you don't need to use functions at all. The formula you need for interest earned during the period is: =IntRate*(EndDate-StartDate)/365 Regards, Fred. "Nth" wrote in message ... Hi, I'm having trouble with an Excel financial function mainly because I don't really understand the financial jargon that is being used to explain it. I want to check that a) I am using the right function and then b) that I'm using it correctly. I'll try to explain the situation in layman's terms : My friend, Mr X, placed some money in a current account in the bank where his money gains no interest. I told him he'd be better off using a savings account where - even if the interest is small - he would make a serious benefit in the long term because the amount in there is quite important and it stays in over a long period. So now I'm trying to prove this to him using Excel. The interest is calculated from day to day. So I'm using the ACCRINTM function calculating (or so I hope) the interest accrued between two movements. Let's say the savings account pays an annual rate of 3.5% and is 1. Opened on Jan 1st with 1000 Euros. 2. On Jan 15 , I pay a bill for 500 Euros. 3. On Jan 30th, I receive a cheque for 400 Euros. I'm recording each movement on a row by row basis, using for "issue" the date of the movement, using for "settlement" the date of the next movement, using for "par" the balance remaining in the account between the two movements, and on the last row, for the "settlement" I use todays date. ACCRINTM ( Date(2008; 01;01) ; Date(2008; 01;15) ; 3.5% ; 1000 ; 4 ) ACCRINTM ( Date(2008; 01;15) ; Date(2008; 01;30) ; 3.5% ; 500 ; 4 ) ACCRINTM ( Date(2008; 01;30) ; Date(... Now ) ; 3.5% ; 900 ; 4 ) Then I sum all the results and I tell Mr X that that's the amount he lost! But am I right? TIA Nth |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
So should that not be
=IntRate*(EndDate-StartDate)/365 * Amount ? No Ive just pasted that in and its obviously not right either. ...... Nth |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
I've posted an image here to show what I'm doing in my table.
http://www.fransysco.net/interest/interests.gif Ther's examples in the rows. It seems to make sense. ------------------------------------------------------- Microsoft says : a.. ACCRINTM is calculated as follows: Which is pretty much the same as what your wrote, except that the par (Amount) is included.) whe a.. A = Number of accrued days counted according to a monthly basis. For interest at maturity items, the number of days from the issue date to the maturity date is used. D = Annual Year Basis. Example 1 2 3 4 5 6 A B Data Description April 1, 2008 Issue date June 15, 2008 Maturity date 10.0% Percent coupon $1,000 Par value 3 Actual/365 basis (see above) Formula Description (Result) =ACCRINTM(A2,A3,A4,A5,A6) The accrued interest for the terms above (20.54795) Nth |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
Well, pasting a snippet form a MS help page was not a good idea.
The page is illegible and there seems to be an attached file include which will maybe scare off some XL gurus. No harm intended, I assure you. Nth |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
Pls check this out
http://www.fransysco.net/interest/interests.gif Looks like your suggestion and the ACCRINTM function do the same thing. They come up with the same results. Nth |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
That's because you're using intervals that are exactly 6 months apart, which
happens to match the bond payment period. Try them with any other period, like 15 days which you used in your initial example. Regards, Fred. "Nth" wrote in message ... Pls check this out http://www.fransysco.net/interest/interests.gif Looks like your suggestion and the ACCRINTM function do the same thing. They come up with the same results. Nth |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
Try them with any other period, like 15 days which you used in your initial example. Hi Fred, Nice to see you back, There you go .... a new pic with irregular dates, http://www.fransysco.net/interest/in...ular_dates.gif Theres a difference between the ACCRINT function - and the - ACCRINTM function. ACCRINT requires intervals. But I'm using ACCRINTM which has a maturity ( = settlement ?) date. So there's an issue date and a settlement date, but no (other) periods. Nth |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
You're right. ACCRINTM should give the same results as my formula. I, of
course, was thinking of ACCRINT, which is substantially different. Sorry for leading you astray. Fred. "Nth" wrote in message ... Try them with any other period, like 15 days which you used in your initial example. Hi Fred, Nice to see you back, There you go .... a new pic with irregular dates, http://www.fransysco.net/interest/in...ular_dates.gif Theres a difference between the ACCRINT function - and the - ACCRINTM function. ACCRINT requires intervals. But I'm using ACCRINTM which has a maturity ( = settlement ?) date. So there's an issue date and a settlement date, but no (other) periods. Nth |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please check my use of the ACCRINTM function.
Ouf !
Now Mr X is going to have to sit up and take notice ! Bye and thanks. Nth "Fred Smith" a écrit dans le message de news: ... You're right. ACCRINTM should give the same results as my formula. I, of course, was thinking of ACCRINT, which is substantially different. Sorry for leading you astray. Fred. "Nth" wrote in message ... Try them with any other period, like 15 days which you used in your initial example. Hi Fred, Nice to see you back, There you go .... a new pic with irregular dates, http://www.fransysco.net/interest/in...ular_dates.gif Theres a difference between the ACCRINT function - and the - ACCRINTM function. ACCRINT requires intervals. But I'm using ACCRINTM which has a maturity ( = settlement ?) date. So there's an issue date and a settlement date, but no (other) periods. Nth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ACCRINTM() function | Excel Worksheet Functions | |||
Date function in ACCRINTM requires date format not available | Excel Worksheet Functions | |||
function to check rows | Excel Worksheet Functions | |||
Check my IF function | Excel Worksheet Functions | |||
check box function? | Excel Worksheet Functions |