stefanoste78
Member
In column "J" I would need to determine the days to calculate the interest rate (principal "I" * interest rate "H") / 356 "or 366 if the year is a leap year".
I should start from the expiry date of the "C" security to identify the period of interest in which today's date falls and then determine the inteccorente days from the beginning of this period to today.
I tried to create a formula that counts the days (column j). It seems to be fine except when the day today corresponds to the last day of the period (see cell c3).
Could you help me find the solution?
example:
sum available: x
purchase date: 20/1/2010
title purchased: interest at 2% "they are all at annual rate", maturity 1/1/2030 (means that the interest period goes from 1/1 / n-1 to 1/1 / n, therefore in the specific case (1 / 1 / 29-1 / 1/30; 1/1 / 28-1 / 1/28 so on).
Today 3/23/2020 I decide to sell the title.
I will be entitled to the portion of interest accrued that I have not collected because the interest is paid annually (at the end of the year of accrual of the interest which is 1/1 for this title).
Today is 23/3/2020
today falls within the coupon period: 1/1 / 20-1 / 1/21
I will be interested in the period: 1/1 / 20-23 / 3/2020
Whoever buys the title will have the rest of the interest, that is: 23/3 / 2020-1 / 1/21
The interest calculation is obtained by the formula: (C "principal" * 2% "interest rate") * t (time in days) / 356 "if the leap year instead of 356 I have to put 366.
When I decide to sell the title "in the formula we will always use today ()", knowing only the expiry date of the title, I will have to get the period of interest (in the example: 1/1 / 20-1 / 1 / 21) to determine the exact days from the beginning of the period up to today (to be included in the formula).
I hope I explained myself
Thank you
I should start from the expiry date of the "C" security to identify the period of interest in which today's date falls and then determine the inteccorente days from the beginning of this period to today.
I tried to create a formula that counts the days (column j). It seems to be fine except when the day today corresponds to the last day of the period (see cell c3).
Could you help me find the solution?
example:
sum available: x
purchase date: 20/1/2010
title purchased: interest at 2% "they are all at annual rate", maturity 1/1/2030 (means that the interest period goes from 1/1 / n-1 to 1/1 / n, therefore in the specific case (1 / 1 / 29-1 / 1/30; 1/1 / 28-1 / 1/28 so on).
Today 3/23/2020 I decide to sell the title.
I will be entitled to the portion of interest accrued that I have not collected because the interest is paid annually (at the end of the year of accrual of the interest which is 1/1 for this title).
Today is 23/3/2020
today falls within the coupon period: 1/1 / 20-1 / 1/21
I will be interested in the period: 1/1 / 20-23 / 3/2020
Whoever buys the title will have the rest of the interest, that is: 23/3 / 2020-1 / 1/21
The interest calculation is obtained by the formula: (C "principal" * 2% "interest rate") * t (time in days) / 356 "if the leap year instead of 356 I have to put 366.
When I decide to sell the title "in the formula we will always use today ()", knowing only the expiry date of the title, I will have to get the period of interest (in the example: 1/1 / 20-1 / 1 / 21) to determine the exact days from the beginning of the period up to today (to be included in the formula).
I hope I explained myself
Thank you