Hyperformula for Mortgage calculation - not working as I was expecting

I’ve been using formulas in the google sheet for the mortgage calculations but then I need a solution to copy the formulas for new rows and that’s working using App Script and then I need a trigger. This working but looking for a more simple solution and added a code->hyperformula field looks like it should work but not seeing the expected results - see image below:

The PMT is calculated in the table but just for the last record and the result is not accessible in the App UI when I add the field to the detail screen. I also don’t see the new field in the google sheet - should I as I see Row ID field if I add that field.

why do you have a - in front of pmt? mathematically I would use *-1… just me

PMT function returns a negative value because this represents payments being made from you to your lender.

I’m going to guess that the extra formatting in your Mortgage Amount column is throwing it off. Are applying the formatting in the Google sheet? If so, I would remove all formatting in the Google sheet because glide might be seeing it as text instead of a number. You can still apply that same formatting in the glide data editor when you edit that column, but the underlying number values will be used for calculations.

I would start with that to make sure all lines calculate a value. You may need to resync the sheet to make sure the unformatted values make it back to glide.

To answer your question about not seeing the calculated value in the google sheet…you won’t. Computed columns like that are computed on the end user’s device in real time. They are only used for calculations within glide, and do not hold values, so there is no reason to send the value back to the Google sheet.


Thanks Jeff, that did the trick :slight_smile:

I only see the calculation for net new records and not any existing, but that’s fine since this is still in development.

How would you address the formatting so the number appears as a formatted dollar amount - use a template column to the glide table ?

1 Like

All you need to do is click on the column header in the data editor (or double click), click on edit, then you will see the options to add rounding, thousands grouping, and a pre/post value, such as a dollar sign.

If you look at the documentation there are some animations that show that.

Doesn’t look like the formatting options are there for more complex column types. The ones you mention are there for number. May need to use CONCATENATE in the formula or a template column unless there is a better option.

  • thanks!

I was referring to the ‘Mortgage Amount’ column specifically. According to your screenshots, that column appears to a basic numeric column.

If you are referring to the result of the hyperformula column, then yes, I suppose there isn’t really any formatting options unless it’s part of your formula. I don’t use the hyperformula plugin myself, but I think of it as a quick and dirty way to migrate logic out of a google sheet and into glide. You kind of get what you get out of it.

Since Glide doesn’t have a direct equivalent of a PMT function, I would stick with the hyperformula in your case. Instead of formatting the result with a template column, I would attempt to use a math column with an X in the formula, and replace X with your hyperformula result. Hopefully it will give you the proper formatting options for a numeric value.

Thanks Jeff - that makes sense. I’ve now added a math column and it’s working well.
Much appreciated!

1 Like