About TrimPath stuff, like Junction, Next Action, JavaScript Templates, TrimQuery, TrimSpreadsheet, etc...
You are not logged in.
I am trying to build a mortgage calculator into one of my spreadsheets. I have found a number of web pages that show formulas for calculating an (American) mortgage:
http://www.moneychimp.com/articles/finw … rtgage.htm
http://www.mtgprofessor.com/formulas.htm
http://mathforum.org/library/drmath/view/54623.html
http://www.hughchou.org/calc/formula.html
There are, of course, plenty of existing web-based mortgage calculators, but I would like to integrate the formula directly into another spreadsheet that I'm using. However, when I try to translate the above formulas into something usable on NumSum, I always come out with the wrong value (I know because I have run the same calculation on a number of existing web based calculators).
Can anyone help me come up with an accurate formula that will work in NumSum?
Thanks!
Offline
I believe the problem is that there's no working exponent function in numsum.
Offline
You can in fact use all kinds of JavaScript functionality in NumSum cells. E.g. an exponent function (power of e = 2.71828183) or a power function:
A1: 2
A2: 3
B1: =Math.exp( A1 )
B2: =Math.pow( A1, A2)
Using the power function you can also calculate all kind of roots:
B3: = Math.pow( A1, 1/A2)
Inputting the JavaScript functions must be done more carefull than otherwise, as case counts:
e.g. math.pow , math.Pow or Math.Pow will result in a #VALUE error as the correct input is Math.pow
More JavaScript math functions can be found here: http://www.javascripter.net/faq/mathfunc.htm
Note: this last link I found on the TrimPath forum (TrimPath being what NumSum is created with), just go back to the Index of this forum to get there.
Offline
When I use these javascript commands and try to save, the numsum spreadsheet says "Table data invalid formula". Any ideas?
Thanks
Offline
Thank you Ahab for the explanation and links.
The only work around for the "Table data invalid formula" error which prevents saving any spreadsheet using a JavaScript function of any kind I've been able to figure out is to put a " in front of the formula converting it to a string before saving, and then editing all formulas to remove the quote for use, and put them back again before saving the spreadsheet. This is quite cumbersome if you need to use this kind of formula in more than one cell.
Is any development going on with numsum? The inability to do interest calculations really limits the kind of spreadsheets that work in numsum. Google spreadsheets are a nice alternative, but other than this problem, and the number formatting bug where changing the number of decimal places doesn't work, I really like the look, formatting, and printing of NumSum spreadsheets over the Google ones.
I copied the example from this thread to a test spreadsheet:
http://www.numsum.com/spreadsheet/show/70234
which illustrates the problem. If you delete the quote mark at the beginning of the formula in cell A3, you can no longer save the spreadsheet. You can however see what 2 raised to the power of three is.
I'm not sure I want to try this with the annuity formula I'd like to use in one of my spreadsheets:
w = [P * Math.pow(1 + r,n-1) * r] / [ Math.pow(1 + r,n) - 1]
(I know this isn't valid for use in a spreadsheet as typed here; I just think it's just easier to read this way.)
Offline
P.S.
I've noticed that if you format the result of one of these formulas as currency, NumSum won't let you edit it back into a string by just putting a " in front or around the line. The cell type remains forced to a number. The only workaround I've found it to clear the cell, copy another text cell into it which seems to change the type back to string again.
Is this another bug in NumSum?
Offline