Loan Amortization Schedule

Question

Looking for a Loan Amortization Schedule that will allow me to input random principle payments throughout the entire.

Example:

If I have an extra $1000 to put towards the principle in August I can put that in and not have it become a set payment every month.

Right now everything I find only allows me to setup a standard every month extra payment.

Any links to something like that or help with it would be greatly appreciated.

Thank You In Advance

joelb95 · Answer

Not that it matters a ton, but did the worksheet/formulas help you?&nbsp; And if you found something better, what was it?

joelb95 · Answer

Do you have a particular format in mind? If I were to do it across multiple cells, it would go something like

principle amount =

interest_rate =

periods =

ordinary_payment = pmt()

first_date =

date = edate(first_date,sequence(periods)

scheduled_payment = if(prior_balance>0,ordinary_payment,0)

additional_payment = (whatever value you want)

current_period_interest = if(prior_balance>0, prior_balance*interest_rate),0

total_principle_payment = additional_payment+(schedule_payment-current_period_interest)

closing_balance = prior_balance-total_principle_payment

total_scheduled_payments = sumifs(scheduled_pmts_col, date_col<=date)

total_additional_payments = sumifs(additional_payments, date_col<=date)

total_interest_paid = sumifs(interest_col, scheduled_pmts_col, date_col<=date)

total_principle_paid = total_additional_payments + (total_scheduled_payments-total_interest_paid)

Where there are blanks after the equal sign, that means you can either a) name the cell and enter a vale in that cell or b) insert a reference to the value you want (like the cell next to it).

I would strongly suggest using a table so that you can sort and what not, but you can use the formulas described in just an ordinary sheet.

If you use a table for the additional payments, rather than entering a value/reference for each additional payment, you can do

additional_payment = sumifs(additional_payment_amt_acol, (other_date_col<=date)*(other_date_col>edate(date,-1)))

The only downside of this method is that if you are interested in more precise interest adjustments based on the date of the additional payment, this formula won't do it (but could be modified if you needed it to).

Any other solution is really just a re-wrapping of this suggestion. You can always calculate the periodic payment in advance, but interest must be calculated based upon the outstanding balance from the prior period. Once you know that number, you can determine how much of the scheduled payment reduced principle. If you want to make additional payments, you just reduce the principle further.

And in case it is non-obvious, the reason for the dynamic formula for the date is that it will automatically change size based upon the criteria you use. If you just have the remaining formulas reference the date col (e.g. $a$5#) and place them next to it, you can fill down all of the formulas to match the size. You just need to delete everything after the first row of formulas if you want to repeat the fill down.

All of the other cell references can be normal, like the prior balance is just E4 if your current ending balance would be E5. No need for offsets, indirects, or anything fancy. The first row would get a slightly different formula if you have to worry about headers, but that is not a big deal. Just change the prior balance reference to be conditional, i.e. if(not(isnumber(prior_balance)), original_balance, prior_balance).

joelb95 · Answer

And p.s. this entire sheet can be made with a single formula if that is your thing, but the way I write it would require you to use a table for the additional payments.&nbsp;p.p.s. here you go.&nbsp; The attached file has both an incell formula version and a named lambda version.&nbsp; There is room for change, certainly, but I think it should function on a base level.&nbsp;=LET(
    total_periods, months_of_payments + years_of_payments * 12,
    monthly_rate, IF(
        OR(ISBLANK(effective_rate), ISOMITTED(effective_rate)),
        yearly_stated_rate / 12,
        (1 + effective_rate) ^ (1 / 12) - 1
    ),
    adj_future_value, IF(OR(ISOMITTED(future_value), ISBLANK(future_value)), 0, future_value),
    regular_payment, PMT(monthly_rate, total_periods, present_value, adj_future_value),
    is_priniciple_reduction, present_value &gt; future_value,
    adj_payment, IF(is_priniciple_reduction, -regular_payment, regular_payment),
    has_additional_payments, OR(
        NOT(ISBLANK(additional_payment_amount_col)),
        NOT(ISOMITTED(additional_payment_amount_col))
    ),
    has_principal_increases, OR(
        NOT(ISBLANK(principal_increase_amount_col)),
        NOT(ISOMITTED(principal_increase_amount_col))
    ),
    period_column, SEQUENCE(total_periods),
    date_column, EOMONTH(start_date, SEQUENCE(period_column, , 0)),
    calcCurPeriod, LAMBDA(cur_end_of_month_date, opening_balance,
        LET(
            cur_additional_payments, IF(
                has_additional_payments,
                FILTER(
                    additional_payment_amount_col,
                    (additional_payment_date_col &lt;= cur_end_of_month_date) *
                        (additional_payment_date_col &gt;= EOMONTH(cur_end_of_month_date, -1)),
                    0
                ),
                0
            ),
            cur_periodic_interest, opening_balance * monthly_rate,
            cur_increases_to_principal, IF(
                has_principal_increases,
                FILTER(
                    principal_increase_amount_col,
                    principal_increase_date_col &lt;=
                        cur_end_of_month_date * principal_increase_date_col &gt;
                        EOMONTH(cur_end_of_month_date, -1)
                ),
                0
            ),
            cur_payment, MIN(
                (opening_balance + cur_periodic_interest + cur_increases_to_principal),
                -regular_payment
            ),
            cur_scheduled_payment_amount_applied_to_principal, cur_payment - cur_periodic_interest,
            cur_total_payments, cur_payment + cur_additional_payments,
            cur_total_increases_to_principal, cur_periodic_interest + cur_increases_to_principal,
            closing_balance, ROUND(
                opening_balance - cur_total_payments + cur_total_increases_to_principal,
                2
            ),
            values_through_additional_payments, HSTACK(
                cur_end_of_month_date,
                opening_balance,
                -regular_payment
            ),
            values_through_increases_to_principal, IF(
                has_additional_payments,
                HSTACK(
                    values_through_additional_payments,
                    cur_additional_payments,
                    cur_periodic_interest,
                    cur_scheduled_payment_amount_applied_to_principal
                ),
                HSTACK(
                    values_through_additional_payments,
                    cur_periodic_interest,
                    cur_scheduled_payment_amount_applied_to_principal
                )
            ),
            values_through_closing_balance, IF(
                has_principal_increases,
                HSTACK(
                    values_through_increases_to_principal,
                    cur_increases_to_principal,
                    closing_balance
                ),
                HSTACK(values_through_increases_to_principal, closing_balance)
            ),
            values_through_closing_balance
        )
    ),
    payment_schedule_headers_prior_to_additional_payments, HSTACK(
        "period",
        "end_date",
        "opening_balance",
        "scheduled_payment_amount"
    ),
    payment_schedule_headers_prior_to_principal_increases, IF(
        has_additional_payments,
        HSTACK(
            payment_schedule_headers_prior_to_additional_payments,
            "additional_payments_applied_to_principal",
            "periodic_interest",
            "scheduled_payment_applied_to_principal"
        ),
        HSTACK(
            payment_schedule_headers_prior_to_additional_payments,
            "periodic_interest",
            "scheduled_payment_applied_to_principal"
        )
    ),
    payment_schedule_headers_through_closing_balance, IF(
        has_principal_increases,
        HSTACK(
            payment_schedule_headers_prior_to_principal_increases,
            "increases_to_principal",
            "closing_balance"
        ),
        HSTACK(payment_schedule_headers_prior_to_principal_increases, "closing_balance")
    ),
    basic_schedule, DROP(
        REDUCE(
            present_value,
            period_column,
            LAMBDA(acc, cur_period,
                IF(
                    TAKE(acc, -1, -1),
                    VSTACK(
                        acc,
                        HSTACK(
                            cur_period,
                            calcCurPeriod(EOMONTH(start_date, cur_period - 1), TAKE(acc, -1, -1))
                        )
                    ),
                    acc
                )
            )
        ),
        1
    ),
    result, VSTACK(
        payment_schedule_headers_through_closing_balance,
        FILTER(basic_schedule, ISNUMBER(CHOOSECOLS(basic_schedule, 3)), "")
    ),
    result
)&nbsp;

Forum Discussion

Loan Amortization Schedule

3 Replies

Resources