Thoughts on creating a 'middle-man' lending worksheet?

Hello All,

I am working on a project for a school club and want to see what the community's thoughts are on building a worksheet that tracks funds lent and funds borrowed would look like. Essentially, I will be a middle bank, borrowing from an outside lender funds and then lending those funds to internal borrowers. I would be able to track the due dates for the money borrowed as well as track the maturity dates for the money lent out.

Where I am stuck on is a way to build the worksheet for a long period of time, say 20 days, and be able to accurately track the principal lent out, maturity date for the lender who I have borrowed and the borrower who borrowed from me, the interest rate paid out/received, etc. Please let me know if you have any examples or thoughts on this. Thank you for giving me you time.

Best regards

.
Here are a few resources of free templates that will fit your needs :

http://templatelab.com/amortization-schedule/

https://www.excel-easy.com/examples/...-schedule.html


Presuming you are borrowing money from a lender, then loaning out that money to others at a higher rate of interest ? Whether you call yourself a "middle man" or
anything else, you are still representing yourself as a loan originator. So ... any existing template that covers amortization, payment schedule, etc. will fit your needs.

My initial thought is to use a template sheet for each individual you are loaning money to. Keeps it neat, clean and simple. Then if you want an overview of all the
loans, you can create a Master Sheet that pulls the highlights from each of the template sheets to create that single view of everything.

Hi Logit,

Thank you for the resources. I had a typo in my initial response and left out an extra 0. I am looking to create a 200-day or so spreadsheet which would chart the borrowing and loaning from both perspectives of the bank and then of myself. The spreadsheet would thus scale pretty quickly, also because internal borrowers would have several loans outstanding at once. I am thinking of a way where the dates go horizontally and then the borrowers on column A. Then there would be interest x day going across the days as long as there is a maturity. Do you think there is a better way to do that?

.
For the most part, any design you feel will best serve your needs would be fine. BUT ... the more convoluted a spreadsheet design is, the harder it is to
create formulas or macro code to accommodate your needs.

My impression is you seem to be indicating the desire to have everything displayed on a single sheet. If your needs can be met in this manner, overall the
best design will be to keep it very simple. When you place lots of "stuff" on a single sheet it can become cumbersome and a burden to interpret.

A different approach might be to have a separate sheet for each individual who receives a loan from you. Then you could create a "Dashboard" sheet that
would display the highlights of all the activities / numbers.

From my experience I sometimes begin the design process in my mind and it quickly blossoms into a mountain. Then I'll step back, realizing I am overthinking
the project .... I'll place just a few things on a single sheet (the bare minimum) to get the project going. Afterwards, if necessary, I'll add a few bells and whistles
from my 'overthinking period'. In any case ... that seems to work for me.

I wouldn't expect your first design to be the final design, unless you have years of experience with Excel / VBA. A project usually gets modified several times.

My 2 cents.