Page 2 of 2 FirstFirst 1 2
Results 11 to 12 of 12

Thread: Loan Schedule - List.Generate (needs Opening Balance calculation)

  1. #11
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    26
    Articles
    0
    Excel Version
    Office 365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Ricky,

    Imke's post looks to Buffer when she is referencing other tables (or presumably lists). The amortization algorithm is merely referencing the prior record. I don't see a place to put in a List.Buffer or Table.Buffer, so it may not apply. I will be trying out her suggestion in List.Generate or List.Accumulate routines that i have.

    I did put in a List.Buffer into the List.Accumulate version of the algorithm, but I suspect that it did not do anything as you have told me that the List.Generate and the List.Accumulate is about the same speed. In my experience, if the Buffer helps then I use it. Otherwise I take it out. So I would probably take it out if I were to use that version.

    I agree with you on using List.Generate. I think it is the better version.

    Thank you for the compliments. I hope you post some other challenges. They are quite fun.

    I work in accounting and am using this code to support the 842 accounting standard. In a prior version of the code, I did not adjust the final payment because it was immaterial to the journal entry being booked. And I was going to argue that with the auditors. But putting in that last step avoids the argument. So thank you too.

    Best Regards,
    Mike

  2. #12
    Acolyte ExcelQuestion's Avatar
    Join Date
    May 2018
    Posts
    22
    Articles
    0
    Excel Version
    Excel 2013 Professional Plus
    Hi Mike,
    It's good to hear your thoughts about List.Buffer / Table.Buffer. Yes, in this scenario, the code references within the current list so probably that's why it didn't make any difference.

    I just ran another of round tests using the previously posted codes:
    100+ new Tranche ID's at varying Period lengths, Principal in many $ millions, output of approx 30,000 rows...
    1) List.Accumulate (with List.Buffer) vs
    2) List.Accumulate (removed List.Buffer) vs
    3) List.Generate
    Results were the same in all 3 cases. Same lighting quick refresh.

    This challenge is fun, indeed, especially as your ideas came back so quickly also. I do have other challenges and will be in upcoming new posts. Perhaps, using Buffer will find it's way into the upcoming code(s).

    Ending at $0 for the final payment is the icing on the cake. Shows true professionalism and credibility were hard at work. You did it!

    The output leads to the full set of journal entries by period for the entire loan. Accounting could post them all in advance and integrate this data into their cash flow forecast.
    1) Dr. Cash | Cr. Long-Term Debt | (Period 0)
    2) Dr. Interest Expense | Dr. Long-Term Debt | Cr. Long-Term Debt | Cr. Cash | (Period 1 - 119, same $ values)
    3) Dr. Interest Expense | Dr. Long-Term Debt | Cr. Long-Term Debt | Cr. Cash | (Period 120, final, rounding adjusted $ values )

    That's why your code rocks!

    Many thanks again, Mike

    Best regards,
    Ricky
    Last edited by ExcelQuestion; 2019-06-09 at 06:30 PM.

Page 2 of 2 FirstFirst 1 2

Tags for this Thread

Posting Permissions

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