Loan Payment Table

1

Hi, I am hoping someone in the community can help me solve this… I am trying to create a variable loan payment table. I was able to get the most of the formulas to work for what I want to do, except for one thing. I am trying to figure out if there is a way that I can have ‘Balance Column’ (starting with Row2) populate with the data from the ‘Ending Balance’ of the previous Row.

Here is a screenshot of the table…

Screenshot 2021-05-18 180012
Screenshot 2021-05-18 180012830×286 40.7 KB

2

Hey @Priscilla_Durkin ,

I would try something like this. This works off a Row Id column and assumes you aren’t going to be changing the rows, deleting the rows, sorting the rows - if you have a different use case (and are going to be consistently deleting or sorting the rows in different orders) then this might not work. If thats the case let me know and I can help you out further!

3

hi @Priscilla_Durkin

When asking the community for help on something like this its always better if you create a sample document. Anyway, the previousRow issue could maybe also be avoided using the Power() function in Coda, not sure if you did use it

If the power() function is not relevant in this case, then this might work for you

thisTable.Balance.Nth(Find(thisRow,thisTable )-1)

it avoids the RowId issue @Scott_Collier-Weir mentions.

image
image2078×783 99.1 KB

Best, Christiaan

1 Like
4

Scott,

I can’t actually see the doc you added because it says I need permission. I was hoping to group the table by year but I don’t have to. Thank you for your help!

6

Hey there!

I Actually like @Christiaan_Huizer solution better - Seems a little more flexible. You should still be able to group and have this work.

Heres the document again, I included both the RowID solution and @Christiaan_Huizer 's solution as well in one spot! Let me know if you run into more problems

If you cannot access the embedded doc, try this → DOC LINK

7

hi @Scott_Collier-Weir , thanks for putting it nicely together.

my main concern is twofold:

  • the function Power() avoids a most cases the need for a previousRow logic
  • interest calculations in Coda follow a different path than in any spreadsheet, though what we see is often an effort to make use of Coda as if it is a spreadsheet. This causes all sorts of pain. I provide an example in this doc in my gallery on the two approaches:
1 Like
8

Hey @Priscilla_Durkin ,

I would really look through @Christiaan_Huizer solution and ideas. Coda really becomes much more powerful when you attempt to take advantage of Coda’s unique structure and capabilities rather than attempting to mimic spreadsheet logic.

The solutions I provided in my example doc should work just fine for your specific question, but in the long run I would look at restructuring your document on a larger level - may take some investment/time but believe it will be worth it in the long run.

2 Likes