FIFO Revolving Line of Credit Tracker with Interest

[SusanJohnst]

Hi!

I'm trying to create a FIFO (First in, First Out) Revolving Line of Credit Tracker in Excel. We buy "short-term" notes (aka, lines of credit) and pay back P&I throughout the month which gets applied to the oldest note first. The rates for each note fluctuates with WSPRIME.

I've attached a picture of what I started. There has to be a better way to do this though.

Any takers?

Thanks!!

 

[Rijnsent]

Hi there,
welcome to the forum. I'd love to help, do know my way around financial calculations, FIFO etc, but have a hard time trying to understand what you are trying to achieve/what your challenge is? Do you want the sheet to be easier to maintain, the data ordered smarter, some fancy chart-ideas with it or something else? I'd gladly help finding "a better way", but do need a bit of guidance what you envision/what you see as the biggest challenges.
Thanks,
Koen

 

[SusanJohnst]

Hello, thanks for looking into this. We're looking for all the above (data ordered smarter, fancier, easier to maintain, etc). Mainly make it more efficient so when we add a principal payment to the sheet, it get's applied to the correct Note and gives us the correct interest for that particular payment.

 

[Rijnsent]

Hi there,
if you want to do it full on professional you'd probably need to use VBA and/or PowerQuery (both part of Excel). Secondly, you'd create 2 input tables and leave the rest to be calculated (example in dutch with some bogus numbers, but I hope you get the idea).

Input 1:

Map1
	B	C	D	E
3	STN #	STN date	STN amount	STN Rate
4	2019-1	18 april 2019	730.000,00	5,50%
5	2019-2	2 mei 2019	600.000,00	5,50%
6	2019-3	3 mei 2019	20.000,00	5,50%
Blad1

Input 2:

Map1
	B	C	D
10	Payment	Payment date	Principal
11	P01	26 april 2019	60.000,00
12	P02	17 juni 2019	300.000,00
13	P03	9 juli 2019	300.000,00
14	P04	21 augustus 2019	300.000,00
Blad1

Output 1:

Map1
	B	C	D	E	F	G	H	I
17	STN	date	payment	date2	STN rate	days	principal	interest
18	2019-1	18 april 2019	P01	26 april 2019	5,50%	8	60.000,00	1.234,00
19	2019-1	18 april 2019	P02	17 juni 2019	5,50%	80	300.000,00	23.456,00
20	2019-1	18 april 2019	open	11 augustus 2020	5,50%	90	300.000,00	56.000,00
21	2019-2	2 mei 2019	open	11 augustus 2020	5,50%	450	600.000,00	56.000,00
22	2019-3	3 mei 2019	open	11 augustus 2020	5,50%	450	20.000,00	2.222,00
Blad1

And 2 additional outputs for e.g. the payments per STN and the use of the payments (for which STN), basically adding some columns to input 1 and input 2. I would keep this separate from your input, as it will otherwise clutter that input. To get started with PowerQuery, try e.g. this: The Complete Guide to Power Query | How To Excel

Hope that helps,
Koen