Hi,
The issue I'm facing is quite complicated to explain so please bear with me. Hopefully the example at the end will help.
For accounting purposes I need have a few financial instruments (which you can think of simply as loans with an interest amount paid either annually or quarterly or semi-annually depending on what is set as the frequency of payments). These have a start date and an end date. The start date is when the loan was taken and the end date is when it matures (ends).
What I need to produce is the payments that the company owes a certain period from the date of the financial statements (i.e. the financial year end). There are certain slots in which these payments fit (i.e. due in <6months, 6-12months, 1-2yrs, 2-5 yrs, >5yrs, etc)
Hence I need an excel formula which is able to figure out how much is due and slot it in the respective column depending on the period due depending on the year of the financial statements. I know it sounds very confusing but hopefully the example below will help.
Say I have a loan of $50,000 which started on 1 Jan 2014 and ends on 30 June 2017 and interest is paid annually. Say the interest is $1000 (due annually). If the year end of the financial statements is 31 Dec 2014 then I would have the following:
1st interest would be paid on 31/12/14. The next interest payment would be due a year from the date of financial statements on 31/12/15.
The next payment would be due on 31/12/16, which is 2 years from the financial statement date. There would be no more interest payment as the frequency of interest payment is annual (and there is not a full year period remaining as the loan ends on 30 June 2017)
As at 31 Dec 2015 this would need to presented as follows:
Amount due in:
<6 months
6-12 months $1,000
1-2 yrs $1,000
2-5 yrs
>5yrs
Any help would be greatly appreciated. Thanks!
The issue I'm facing is quite complicated to explain so please bear with me. Hopefully the example at the end will help.
For accounting purposes I need have a few financial instruments (which you can think of simply as loans with an interest amount paid either annually or quarterly or semi-annually depending on what is set as the frequency of payments). These have a start date and an end date. The start date is when the loan was taken and the end date is when it matures (ends).
What I need to produce is the payments that the company owes a certain period from the date of the financial statements (i.e. the financial year end). There are certain slots in which these payments fit (i.e. due in <6months, 6-12months, 1-2yrs, 2-5 yrs, >5yrs, etc)
Hence I need an excel formula which is able to figure out how much is due and slot it in the respective column depending on the period due depending on the year of the financial statements. I know it sounds very confusing but hopefully the example below will help.
Say I have a loan of $50,000 which started on 1 Jan 2014 and ends on 30 June 2017 and interest is paid annually. Say the interest is $1000 (due annually). If the year end of the financial statements is 31 Dec 2014 then I would have the following:
1st interest would be paid on 31/12/14. The next interest payment would be due a year from the date of financial statements on 31/12/15.
The next payment would be due on 31/12/16, which is 2 years from the financial statement date. There would be no more interest payment as the frequency of interest payment is annual (and there is not a full year period remaining as the loan ends on 30 June 2017)
As at 31 Dec 2015 this would need to presented as follows:
Amount due in:
<6 months
6-12 months $1,000
1-2 yrs $1,000
2-5 yrs
>5yrs
Any help would be greatly appreciated. Thanks!
