Loan Payment Calculations

[sixlevel]

I need to to perform functions that will calculate payments based on a loan amount, interest rate and term. This is an easy function in excel: =(MAX(Payments!M3:M32))/12  BUT, can I get some suggestions as to how to do this in Caspio?

 

Secondly, I'd like to get an amoritzation schedule showing payments and declining balance over a period of time such as 1 year, 5 years, etc.

 

Has anyone done this in Caspio?

[Jan]

Hello Larry,

 

If I understand correctly, M3:M32 means all records, then the formula finds the MAX of all records and divide it by 12. Is it correct?

If it is correct, you can use an Aggregation.

Yon can add an Aggregation, select you field, select the Formula option and enter the following formula:

MAX/12

I hope, it helps.

 

 

Could you please elaborate on an amortization schedule? Do you want to display the whole sum that should be paid in 1 year? Or do you want to display values month by month?

[sixlevel]

I wish it were that simple and I was not clear enough. The excel function Payment is used as follows to determine monthly payments when fed a loan amount, term and interest rate. Example: PMT =PMT( 5%/12, 60, 50000 ) with answer being $943.56.

 

I'm looking to create an amortization schedule that lists out each month the declining balance and amount attributed to principal and interest. 

Because caspio has limited excel functionality, I'm going to have to find another way externally ... I think

[Jan]

Hello Larry,

 

Have you a formula for calculating of PMT?

If you can write it, I think, I will be able to "convert" the formula to the code for a Calculated field.

Do you want to use fields of your table as arguments for the formula?

[karikounkel]

The formula that's in Excel uses the PMT function and it looks like this:

PMT(AnnualInterestRate/PaymentsPerYear,Years*PaymentsPerYear,Amount). 

Then it uses the PPMT function to get the principle total:

PPMT(AnnualInterestRate/PaymentsPerYear,A7,Years*PaymentsPerYear,Amount)    A7 = which payment number you're on.

Then it closes with IPMT function to get the interest total: 

IPMT(AnnualInterestRate/PaymentsPerYear,A7,Years*PaymentsPerYear,Amount)    A7 = which payment number you're on.

Those last two look the same, but they aren't.

The initial information in the process is: ANNUAL INTEREST RATE, YEARS TO PAY, PAYMENTS PER YEAR, TOTAL AMOUNT. The goal looks like what's found here: https://www.excel-easy.com/examples/loan-amortization-schedule.html

 

What I did so far is create a table that's linked by a one-to-many relationship with the vehicle table. In my child table, I have changed the initial information to:
PRINCPLE AMOUNT (LOAN OR LEASE AMOUNT)
NUMBER OF MONTHS TO PAY (replaces years to pay and payments per year)
ANNUAL INTEREST RATE
START DATE

What I plan to do is calculate the Payment Amount using this calculation in my table:

1. Calculate monthly interest rate = (1+annual interest rate)*(1/12-1).
2. Calculuate monthly payment = (principle*monthly interest rate from #1/1)-1+(monthly interest rate from #1).
3. Calculate principle =monthly payment amount from #2 - (remaining balance which is different after the first month*monthly interest rate from #1)
4. Calculate declining balance due  = remaning balance-princple from line #3.

I'm trying to put it all in the table rather than in a datasheet. Then I'll add a field for payment date, but haven't figured out how to make it just advance monthly just yet.

Edited April 11, 2019 by karikounkel
add receive email notifications about updates to this post

[karikounkel]

I couldn't add "Notify me of replies" by editing, so I'm adding a post. Sorry.