ExcelBanter - Loan Amortization with a rest value

On Sep 15, 7:43 am, I wrote:
On Sep 15, 7:32 am, Boskantertje
wrote:
I need a Loan amortization table in which is intergated a rest value at the
end of the loan. Is this available?

By "rest value", I think you mean the remaining balance after a
specific number of payments. For example, consider a $10,000 loan at
6% which would amortize to zero after 48 months. The monthly payments
might be (in A1):
=pmt(6%/12, 48, -10000)
If you make only 36 payments, the remaining balance would be:
=fv(6%/12, 36, A1, -10000)

On second thought, that cannot be what you want because, if you have
a loan amortization schedule ("table"?), the remaining balance is a
natural part of the schedule.

I wonder if you want the last payment to include all of the remaining
balance, not just the regular monthly payment. In that case, if the
regular monthly payment is in A1 and the number of payments in A2 and
the annual interest rate is in A3, and if your schedule starts in row
6 with the payment number in column A and the outstanding balance in
column F, then the payment cell could be computed as follows (copying
down the entire schedule):

=if(A5=$A$2, F4*(1+$A$3/12), $A$1)

That says: if the current payment number is the last payment number,
then the payment is the outstanding balance (in the previous row) plus
the interest for the month; otherwise, the payment is the regular
payment.

Of course, the exact syntax depends on the design of your amortization
schedule.

PS: You might also want to account for the possibility that the
outstanding balance has reduced faster than expected, perhaps due to
earlier payments in excess of the regular payment (i.e. prepayment of
principal). You could modify the IF() condition as follows:

=if(or(A5=$A$2, F4*(1+$A$3/12)<$A$1), ..., ...)