+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    05-22-2017
    Location
    Houston, Texas
    MS-Off Ver
    2013/2016
    Posts
    20

    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

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,120

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

    .
    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.

  3. #3
    Registered User
    Join Date
    05-22-2017
    Location
    Houston, Texas
    MS-Off Ver
    2013/2016
    Posts
    20

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

    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?

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,120

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

    .
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1