How to calculate amount of debt remaining over time

[Izzii0x]

Hi guys,

Apologies for the confusing title, I just wasn't sure how to ask my question without explaining the scenario.

Lets say my company was going to fund another company an amount each week (a different amount), and this company would pay me back exactly 1 month after for 1/11th of the amount I funded, and the month after that another 11th etc until they had finished paying it all back 11 months later.

E.g. week 1 £500,
1 month later they would pay back £45.45, and exactly a month after that they would pay back another £45.45 etc.

The problem I am having is that there is a new amount each week and I need to produce a graph which shows my 'exposure' at different times during the year. AKA if I have funded £1m and only been paid back 400k, my exposure = 600k.

I have attached some sample data to show an amount for each week. I'm more looking to see if anyone knows of any formula(s) that could automatically show me my exposure for each date of the year or if I will need to do it manually?

Thank you!
Izzie

[Glenn Kennedy]

maybe this in C2, copied down:

=SUM($B$2:$B$53)-SUM($B$2:B2)

[PaulM100]

Maybe this:
It reflects how much has been paid over time, each month, starting in E2

Please Login or Register  to view this content.

[samba_ravi]

Try below formula
=SUMPRODUCT((MONTH(A2:A53)-1)*B2:B53)/11

[davsth]

I think you need to show what you would like as the final answer, I am not clear what dates you pay back the money. is it the same day of the following month? If so how do you treat the money lent on the 31st of March, when does it get repaid

The table or chart you wish to have is it in weeks or days?

[Izzii0x]

Try below formula
=SUMPRODUCT((MONTH(A2:A53)-1)*B2:B53)/11

So that would be the final result, yes, but I need to show over time (e.g. January has an exposure of x but by the end of February it is y). This is a very useful formula to know though, thanks so much!

I think you need to show what you would like as the final answer, I am not clear what dates you pay back the money. is it the same day of the following month? If so how do you treat the money lent on the 31st of March, when does it get repaid

The table or chart you wish to have is it in weeks or days?

So in that example, 31st of March would be repaid a month later, so 30th April an 11th of whatever was paid on 31st of March would be paid back.

The problem lies where there are many different transactions across the year, so multiple paying back as well as lending more money to take into account. I think I may have solved it manually on excel, but it is still worth seeing if there was an easier way to do it, if anyone happens to know of one.

How I did it manually was to use the table from the first post, do =EDATE(A2,1) to add a month, then followed that formula down the column to see which month the first 11th would be paid back in. I then did =EDATE(C2,1) to add a month to that date.. and so on. Until I had 24 columns, a column of dates followed by the column of how much was paid back (but all *-1 as to see the overall variance). I then put this into 2 columns, date + money in/out, then created a new column called exposure and did a running sum of the money in/out.

[davsth]

I would have done similar to yourself, see attached