Request: Floating Principal Excel Formula

Hello all,

I would like to ask you if somebody could help me with a formula solution, if it is possible. I would like to use an Excel formula for accrued interest of a deposit with floating par value. The example is:

Issue date - 01.08.2014
Settlement date - 01.08.2014
Period - 1 year
Interest rate (annual) - 5%
Principal increase - 100 units monthly
Par value 01.08.2014 - 10 000 units
Par value 01.09.2014 - 10 100 units
Par value 01.10.2014 - 10 200 units
Par value 01.11.2014 - 10 300 units
Par value 01.12.2014 - 10 400 units
Par value 01.01.2015 - 10 500 units
Par value 01.02.2015 - 10 600 units
Par value 01.03.2015 - 10 700 units
Par value 01.04.2015 - 10 800 units
Par value 01.05.2015 - 10 900 units
Par value 01.06.2015 - 11 000 units
Par value 01.07.2015 - 11 100 units

Thank you in advance!

Hello,

Thank you for your reply! I reviewed but I didn't find anything suitable. For a normal deposit (with constant par value) I use ACCRINTM formula, but I don't know how to "fix" the "par" section in order to add constant amount to it depending on the period. For example, if we are on 21st of October 2014, I would like to see the accrued interest for the past period - the sum of the interest of August (on the par of 10 000), September (on the par of 10 100) and the days of October prior to 21st (on the par of 10 200). I tried to write the "par" section with IF formula, but there was an error message. I would be glad if someone help me to write this formula.

Thanks!

You mentioned you used the ACCRINTM formula. The ACCRINTM is basically a formula that does (using the example of monthly terms, and using the default basis of 30/360 days or 1/12 months) is #Months * %p.a. / 12 * par_value

For example, =ACCRINTM("1/8/2014","1/8/2015",5%,10000) yields 500, which is the same as 12 months & 5% p.a. / 12 * 10,000 (also 500)

If the par value increases by a fix amount, you can calculate this as such.
The first par_value of $10,000 will yield 12 months worth of interest;
The 2nd addition to the par value $100 will yield 11 months worth of interest;
The 3rd addition of $100, 10 months of interest; etc. etc.

So, with a 12 months period, you can compute this as
=10,000 * %p.m. * 12 mths + 100 * %p.m. * 11mths + 100 * %p.a. * 10mths + 100 * %p.m.* 9mths + ... + 100 * %p.m. * 1mth + 100 * %p.m. * 0mth (final increase in par value yields 0 interest)
=10,000 * %p.m. * 12 mths + 100 * %p.m. * (11 + 10 + 9 + ... + 1 + 0)

So, to generalise this, the formula you can use is
=par_value * monthly_rate * num_of_months + par_value_increments * monthly_rate * num_of_months * (num_of_months -1) / 2

Portion in blue, see https://en.wikipedia.org/wiki/1_%2B_..._%2B_%E2%8B%AF

So using your example above,
=10,000 * 5% / 12 * 12 + 100 * 5% / 12 * 12 * 11 / 2 = 527.50

See attached for proof of concept.

Hello all,

Thank you for the replies. The table is very useful, but I would like to make this formula more practical - I mean - the ACCRINTM formula has a date element and if I want to change the date in this cell, I could see the accrued interest until this date. Unfortunately, the par value is floating so it's difficult to change it for the formula.

Pali