Repeater - amortization table like functionality

#1

Hello,
I am trying to build an loan amortization table: based on 2 -3 inputs the table will build itself with the required number of rows (say 2 year x 12 payments) = 24 rows and show each row with the new set of dates per each month of each year given the input of the initial payment month.
Is it possible to do with the repeater?

#2

Absolutely. In fact, the simplest thing to do would be to calculate ahead of time all values you need and simply enter them into the repeater as if it were a spreadsheet. Then you wouldn’t need to any calculation in Axure and it would just display your data.

You can also do the calculation in Axure, if you want, so you could have the calculations changed based on inputs from the user, but you’ll have to provide more context and details if you need help with that.

#3

Thank you for such a quick response!
So if I have :
Loan amount: 200,000
Payment amount: 1,000
Payments Date: June 1, 2010
Payments per year 12
Loan, years: 2

With these inputs I want to get a table that will say:
Date |Payment amount | Balance |
(first of the 24 months) | 1,000 | (Loan Amount - Payment amount)
(second of the 24 mo) | 1,000 | (Balance from the previous line - Payment amount
and so on for the 24 months

Can you help me with this example?

#4

Well the simplest way is just enter in the data in 24 rows for the repeater dataset, manually key in the correct amounts.

If you want to calculate it dynamically you’ll need to provide the formula.

#5

It is dynamic, based on the input of a user that I provided, and based on the formula that I provided above.
I obviously not able to set it up in the tool and need help to understand how to set up the repeater to incrememnt based on the previous row results.

#6

It might be easier to consider the remaining balance as a function of the month and payment amount. That is:

remaining_balance = initial_amount - (month_index * payment_amount)

This way each row calculates its value completely independently. You could even just use the row index for this, no need for a separate column. So assuming you had a widget in your repeater called “remaining_balance” where you want to show that, your interaction would look like:

OnItemLoad
If true
    Set text on remaining_balance to: [[initial_loan - (item.index * monthly_payment)]]

Where initial_loan and monthly_payment are global variables. And just make sure your repeater has 24 rows in it. If each month won’t have the same monthly payment, you could add a column that stores each month’s payment amount and then replace the global variable with a reference to that column instead, like: item.payment_amount. Maybe you want to also show the month name, so you add a month column and fill in each month name. You could add on to it:

OnItemLoad
If true
    Set text on remaining_balance to: [[initial_loan - (item.index * monthly_payment)]]
    Set text on month_label to: [[Item.month]]
#7

I am having trouble with the date in formula. I need increment months from the initial month input. I cannot find an expression that allows me something like 12/1/2009 + 1 month = 1/1/2010

#8

Whenever you start getting dynamic with dates in Axure, it starts to look messy but it’s not too complex at the heart of it. Let’s assume you have a variable called date_input where the user inputs a date in the format of mm/dd/yyyy. You could show the date on each row (assuming the first row should be the exact date they entered) as such:

Set text on date_widget to:
    [[(Date.parse(date_input)+0).addMonths(item.index-1).getMonth()]]/[[(Date.parse(date_input)+0).addMonths(item.index-1).getDate()]]/[[(Date.parse(date_input)+0).addMonths(item.index-1).getFullYear()]]

Add this as a another Set Text action to the OnitemLoad case you already have. If you want the first row to not be the date entered but to be the first month after it, change item.index-1 to just item.index.

The reason it looks so long is because we can’t define an arbitrary local variable in Axure so we need to re-parse the date input for each part of the date we want to show (month, day, year), so it looks a lot worse than it is.

Note that this is calendar aware, so if the user chose 12/1/2019 as the start date for the loan, the second row would correctly show 1/1/2020.

#9

Thank you for helping out with this task!

I just tried it and get Nan/NaN/NaN. I do have the input date control formatted as a date and pick the date from a calendar. At this point I copied the formula verbetum and changed the name of the variable to align with mine. What is wrong?

PS. the formula to write into the variable is ‘set variable value to text on widget’. Maybe that is a problem?

#10

I’m pretty sure the issue is related to using an input datepicker. As a troubleshooting step, use a regular text input instead and type in the date and see if that works.

What event are you using to set the variable value? I believe picking a date in a datepicker input does not trigger the OnTextChange event, if you were using that. I think you’re getting NaN because nothing is actually getting sent to the variable. You can also open the debugger in the preview window ( the [x looking icon in the top right) to see what the variable value is while running the prototype.

#11

Yes, the variable is empty.
I will try the text method. What event would work for the calendar picker?

#12

OK, it worked as text.
What would the formula look like if it is years I needed to add not months?

As the next important step in this task: how can I apply a formula to the ever Nth row of the repeater? Example: I have 36 rows which represent 12 months x 3 years; each year (every 12th month) a new rate is applied. How can I pinpoint the 12th month?

#13

Well you would expect OnTextChange to work, but it just doesn’t. I don’t know if it’s an Axure issue or a limitation related to the standard browser implementation of input[type=date]. Until recently some browsers like Safari didn’t even implement the date picker.

Unfortunately the answer is probably to use a button’s OnClick event to grab the text instead of trying to rely on OnTextChange. Maybe you could use OnKeyUp but that won’t be great because it will try to calculate it with every keypress so you’ll probably get NaN or just the expression itself until a valid date is been totally entered.

Or use a custom date picker solution, which is obviously a lot of extra complexity, but there are examples on the forums of that.

#14

OK, it worked as text.
What would the formula look like if it is years I needed to add not months?

As the next important step in this task: how can I apply a formula to the ever Nth row of the repeater? Example: I have 36 rows which represent 12 months x 3 years; each year (every 12th month) a new rate is applied. How can I pinpoint the 12th month?

#15

If you want to add years instead of months, change .addMonths(item.index-1) to .addYears(item.index-1). This will add one year to each row, instead of one month to each row. So instead of 12 months, you’d get 12 years, one year per row.

To do something different on every Nth row, add a second case to your OnitemLoad event. It will look something like:

OnitemLoad
If value [[item.index % 12]] equals 0
  Do new formula

In that new formula you could use (item.index / 12) to determine if it’s the first 12th row, the 24th row, or the 36th. For example on the 24th row (item.index / 12) would be equal to 2 (second year). So you can use that as necessary in your formula. Or if you mean you want a different formula for ranges of years, use a two conditions on your case like:

If value [[item.index]] is greater than 12 AND value [[item.index]] is less than or equal to 24

Lastly, I think I have a solution to the OnTextChange issue. It appears it’s an Axure issue because in both FireFox and Chrome, the input event correctly fires when changing the value of the datepicker. So you can put this in an Open Link (external URL) action on the OnPageLoad event:

javascript:document.querySelector('[data-label=date] input').addEventListener('input',e=>{var d = e.target.value;$axure.setGlobalVariable('date_input',[d.substr(5,2),d.substr(8,2),d.substr(0,4)].join('/'))})

Next name your datepicker widget, then look through the above code and find where it says [data-label=date] and change it to [data-label=newName], using the name you chose for your widget. Next find where it says 'date_input' and change it to 'variableName' using the global variable you made for the date. Be careful that you preserve the quotes and don’t accidentally add or delete one.

How do I get the value of a text field when its type is set to Date?
#16

I haven’t done the last item we discussed, so I will come back on that when I do it.

However I got stuck on a formula, which keeps returning the formula rather than a value. It must be a syntax, but I cannot find an issue.

When I do just one of them, it works, eg
[[(varHydro*((varHydroIncrease/100)(Item.index-1)+1)).toFixed(2)]]
(using math language [[(10((1/100)*(2-1)+1)).toFixed(2)]] --> $10 X 1.01 = $11.01)

When I am trying to sum up all individual items into one big formula, add them to the previous balance and bring it to 2 decimals, it is failing. Where am I going wrong.

[[(varBalance+
(varHydro*((varHydroIncrease/100)(Item.index-1)+1))+
(varAddRent((varHydroIncrease/100)(Item.index-1)+1))+
(varPropTax((varPropTaxIncrease/100)(Item.index-1)+1))+
(varHydro((varHydroIncrease/100)*(Item.index-1)+1))).toFixed(2)]]

#17

When you’re posting your formula here, sandwich your it between triple back-ticks:

\`\`\`
Formula here
Just remove the slashes
\`\`\`

But, I believe, the issue is you have an extra closing parens at the ends

...(Item.index-1)+1))).toFixed(2)]]
                     ^
                     one too many

It was a little easier to see once I indented everything:

(varBalance +
    (
  		varHydro * (
        	(
            	varHydroIncrease / 100
            ) * (
              Item.index - 1
            ) + 1
        )
) + (
  	varAddRent * (
      	(
          	varHydroIncrease / 100
        ) * (
          	Item.index - 1
        ) + 1
    )
) + (
  	varPropTax * (
      	(
          	varPropTaxIncrease / 100
        ) * (
          	Item.index - 1
        ) + 1
    )
) + (
  	varHydro * (
      	(
          	varHydroIncrease / 100
        ) * (
          	Item.index - 1
        ) + 1
    )
)
).toFixed(2)
^
extra
#18

I am so appreciative for your help, you dont even know :slight_smile:

OK, I got this to calculate. Here is where I got so far.
I set varBalance on clicking ‘generate’ where I sum up all 4 fields without increases.
I set the case for row 1 to also calculate all amounts without increases.
Starting row 2, the amounts should be calculated with increases (in the example Amount is set to increase by 3%).
As you see, the Balance is off. Somehow the balance in row 2 is 100 and not 203, however this amount shows on row 3. The balance formula we just worked on is the calculation for row 2 onwards…
We should be very close to solving the overall task, just need to get this bit to work…
Please help!

2020-02-06_15h13_27

#19

It uses that big formula above for balance? What were the values of all the variables in it when you tried it?

My guess is because we’re using item.index - 1, that is 0 in the first row. So having some zeros in there might do that. Try changing it to item.index and see if that is what you expected.

#20

Yes, thats the formula
Amount was set to ‘100’
varIncrease was set to ‘3’
the rest blank for ease of use

I set varBalance on ‘generate’,
then on Case 1 (for the first row to behave differently) of onLoad for repeater (I again set varBalance with just summing 4 amounts - somehow if I don’t the varBalance behaves totally unpredictable and I cannot even describe what it does; if I dont set it on Generate then it shows on first row as 0 - not sure why all of this is calculating this way.
For Case 2 - it is that big formula…