Possible Addendum to the Amortization Seminar - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Possible Addendum to the Amortization Seminar

Arlyn Henken

6 months ago

Hey everyone, I just finished the Amortization Seminar, which I found extremely helpful with a project that I am working on.  Just a little bit of backstory, I am a newly minted bookkeeper, and I am developing some tools that will help me in that endeavor.  There are a couple of things that would be helpful in the Amortization database.

They are a depreciation schedule, which should be easier to put together based on the amortization schedule, since there will not be anything too complicated involved, like random payment amounts or things like that.  Just taking the total value and dividing it out over the items' serviceability period.

The other thing that may be a bit trickier would be a schedule that shows a rolling 12-month balance tied to the amortization schedule.  This would provide a list where it takes the principal amount of the next 12 payments and adds them up each month.  Along side that I need a column that shows the difference between those amounts month to month.

I will add an example excel file of what I am talking about.

	Arlyn Henken OP @Reply 6 months ago

	Alex Hedley @Reply 6 months ago
Like a Moving Average?

	Arlyn Henken OP @Reply 6 months ago
No, it is basically a rolling, but depreciating value that shows what the current liability is for a business in regard to the loan. It would be under the liability section in a balance sheet of a business under Current Portion of Long-Term Debt. The column on the far right is the monthly adjustment that is made to that account.

	Arlyn Henken OP @Reply 6 months ago
I have tried using the philosophy from the video you linked to and swapped the DAvg function for Dsum, but I am not getting the amounts right. Here is the formula I am using, but it does not seem to be adding up the entire range of records: X: Format(DSum("Principal","tblSchedule","PaymentNumber=" & [PaymentNumber] & " AND PaymentNumber<=" & [PaymentNumber]+12),"Currency")

Arlyn Henken OP @Reply

6 months ago

After some online research and some help from another form I was able to get what I needed for part of what I am trying to accomplish, that being getting the 12 month rolling balance based on the monthly principal amount. Here is the sql for the query:

SELECT

    tblSchedule.ScheduleID,

    tblSchedule.LoanID,

    tblSchedule.PaymentNumber,

    tblSchedule.DueDate,

    tblSchedule.Principal,

    sum(tblSchedule_1.Principal) AS Rolling12MonthBalance

FROM

    tblSchedule

    INNER JOIN tblSchedule AS tblSchedule_1 ON (tblSchedule.LoanID = tblSchedule_1.LoanID)

    AND (

        tblSchedule_1.PaymentNumber < tblSchedule.PaymentNumber + 12

    )

    AND (

        tblSchedule_1.PaymentNumber >= tblSchedule.PaymentNumber

    )

GROUP BY

    tblSchedule.ScheduleID,

    tblSchedule.LoanID,

    tblSchedule.PaymentNumber,

    tblSchedule.DueDate,

    tblSchedule.Principal;

Now, I need to figure out how to get the difference between each row. Does anyone know of a way to do that within the same query or should it be handled in another query?

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View