M
Mishanya
I have a question rather on mathematics then query building and would
appreciate any help.
My DB deals with investments portfolios and the question is on building
table (query) of bonds principal and coupon (interest) payments. The bonds
pay principal payments annually, on the same date, in the run of 1 to 10
years, each year according fraction. I.e., if I have face value of 100,000
with principal payments from 10.01.2010 to 10.01.2014 (4 years run, 5
payments), I’ll get 20,000 each January 10 from 2010 till 2014. Also, the
bonds pay coupon (interest) on the remained principal sum annually,
semiannually or quarterly (1, 2, or 4 times a year), each payment is a
remained principal multiplied by fraction of an annual coupon rate, while the
coupon payment dates go accordingly with the principal payment dates – the
same date if annually, the same date and in 6 months if semiannually, the
same date and every 3 months if quarterly. I.e., in the same example if the
coupon rate is 12% and it is paid semiannually, I’ll get 6% on 100,000 in
10.06.2009, 6% on 100,000 in 10.01.2010 (plus 20,000 as the 1st principal
payment), 6% on 80,000 in 10.06.2010, 6% on 80,000 in 10.01.2011 (plus 20,000
as the 2nd principal payment) etc. Basically, it is like loan amortization
repayment method with equal principal plan, only the interest may be paid
more then once a year.
I have tblBonds. It has (among others) fields: BondName,
FirstPrincipalPaymentDate, LastPrincipalPaymentDate, CouponRate,
CouponFrequency. The bond face-value (principal) held in portfolio is
calculated by query based on tblTransactions, but for the matter of
simplicity I’ll assume that tblBonds also has PrincipalSum field.
Now I need to build query (-ies) which output is bond amortization plan
consisting of:
1) Principal payments
2) Coupon payments
3) Sum of aboves grouped by year
So far I have successfully dealt with the principal payments plan:
1) qryPrincipalPaymentDate denormalizes the tblBonds, having BondName field,
8 calculated date fields:
IIf(LastPrincipalPaymentDate>=DateAdd("yyyy",1,[
FirstPrincipalPaymentDate]),DateAdd("yyyy",1, FirstPrincipalPaymentDate,Null)
AS PrincipalPaymentDate1
where DateAdd and the field-names run from 1 to 8 and give, combined with
the First- and the Last- PrincipalPaymentDate up to 10 dates for every bond,
and calculated field PrincipalPaymentSum
PrincipalSum/(Year(LastPrincipalPaymentDate)-Year(FirstPrincipalPaymentDate)+1)
2) qryPrincipalPaymentPlan (Union query) normalizes the previous query:
SELECT BondName, PrincipalPaymentSum, FirstPrincipalPaymentDate FROM
qryPrincipalPaymentDate
UNION ALL SELECT BondName, PrincipalPaymentSum, PrincipalPaymentDate1
FROM qryPrincipalPaymentDate WHERE PrincipalPaymentDate1<>NULL
UNION ALL SELECT BondName, PrincipalPaymentSum, PrincipalPaymentDate2
FROM qryPrincipalPaymentDate WHERE PrincipalPaymentDate2<>NULL etc.
I need help with building coupon payments plan. On the contrary to the
principal payments, the coupon payments frequency vary, so I can’t build
qryCouponPaymentDate in the same manner (and dynamical query with floating
number of calculated dates can’t be used further on in the 2nd normalizing
query). Also principal amortization (derived from calculated principal
payments dates) influences the sum of coupon payment, as the remained
principal sum (unpaid balance) diminishes annually. The query has also to
take into account, that for the most of bonds coupon will be paid before the
FirstPrincipalPaymentDate, as the latter may come in up to a few good years,
while the coupon is paid for the whole bond life.
Another approach is to amortize the coupon rate instead of the principal:
CouponRate (n) = ((CouponRate * (PrincipalSum –
((PrincipalSum/(YEAR(LastPrincipalPaymentDate)-YEAR(FirstPrincipalPaymentDate)+1))*(n-1))) / PrincipalSum
But the question is how to define the n-value, and still the main problem is
breaking down the coupon payment dates calendar.
Any ideas?
appreciate any help.
My DB deals with investments portfolios and the question is on building
table (query) of bonds principal and coupon (interest) payments. The bonds
pay principal payments annually, on the same date, in the run of 1 to 10
years, each year according fraction. I.e., if I have face value of 100,000
with principal payments from 10.01.2010 to 10.01.2014 (4 years run, 5
payments), I’ll get 20,000 each January 10 from 2010 till 2014. Also, the
bonds pay coupon (interest) on the remained principal sum annually,
semiannually or quarterly (1, 2, or 4 times a year), each payment is a
remained principal multiplied by fraction of an annual coupon rate, while the
coupon payment dates go accordingly with the principal payment dates – the
same date if annually, the same date and in 6 months if semiannually, the
same date and every 3 months if quarterly. I.e., in the same example if the
coupon rate is 12% and it is paid semiannually, I’ll get 6% on 100,000 in
10.06.2009, 6% on 100,000 in 10.01.2010 (plus 20,000 as the 1st principal
payment), 6% on 80,000 in 10.06.2010, 6% on 80,000 in 10.01.2011 (plus 20,000
as the 2nd principal payment) etc. Basically, it is like loan amortization
repayment method with equal principal plan, only the interest may be paid
more then once a year.
I have tblBonds. It has (among others) fields: BondName,
FirstPrincipalPaymentDate, LastPrincipalPaymentDate, CouponRate,
CouponFrequency. The bond face-value (principal) held in portfolio is
calculated by query based on tblTransactions, but for the matter of
simplicity I’ll assume that tblBonds also has PrincipalSum field.
Now I need to build query (-ies) which output is bond amortization plan
consisting of:
1) Principal payments
2) Coupon payments
3) Sum of aboves grouped by year
So far I have successfully dealt with the principal payments plan:
1) qryPrincipalPaymentDate denormalizes the tblBonds, having BondName field,
8 calculated date fields:
IIf(LastPrincipalPaymentDate>=DateAdd("yyyy",1,[
FirstPrincipalPaymentDate]),DateAdd("yyyy",1, FirstPrincipalPaymentDate,Null)
AS PrincipalPaymentDate1
where DateAdd and the field-names run from 1 to 8 and give, combined with
the First- and the Last- PrincipalPaymentDate up to 10 dates for every bond,
and calculated field PrincipalPaymentSum
PrincipalSum/(Year(LastPrincipalPaymentDate)-Year(FirstPrincipalPaymentDate)+1)
2) qryPrincipalPaymentPlan (Union query) normalizes the previous query:
SELECT BondName, PrincipalPaymentSum, FirstPrincipalPaymentDate FROM
qryPrincipalPaymentDate
UNION ALL SELECT BondName, PrincipalPaymentSum, PrincipalPaymentDate1
FROM qryPrincipalPaymentDate WHERE PrincipalPaymentDate1<>NULL
UNION ALL SELECT BondName, PrincipalPaymentSum, PrincipalPaymentDate2
FROM qryPrincipalPaymentDate WHERE PrincipalPaymentDate2<>NULL etc.
I need help with building coupon payments plan. On the contrary to the
principal payments, the coupon payments frequency vary, so I can’t build
qryCouponPaymentDate in the same manner (and dynamical query with floating
number of calculated dates can’t be used further on in the 2nd normalizing
query). Also principal amortization (derived from calculated principal
payments dates) influences the sum of coupon payment, as the remained
principal sum (unpaid balance) diminishes annually. The query has also to
take into account, that for the most of bonds coupon will be paid before the
FirstPrincipalPaymentDate, as the latter may come in up to a few good years,
while the coupon is paid for the whole bond life.
Another approach is to amortize the coupon rate instead of the principal:
CouponRate (n) = ((CouponRate * (PrincipalSum –
((PrincipalSum/(YEAR(LastPrincipalPaymentDate)-YEAR(FirstPrincipalPaymentDate)+1))*(n-1))) / PrincipalSum
But the question is how to define the n-value, and still the main problem is
breaking down the coupon payment dates calendar.
Any ideas?