paxtt.blogg.se

' If TCVRng.Value > 0 And IsDate(SdtRng.Value) And IsDate(FdtRng. 'to bypass any intermidate summaty rowrow 'Debug.Print TCVRng.Value, SdtRng.Value, FdtRng.Value First, add some labels in the first column to make it easier to read the worksheet. 'Modify ranges according to your requirement Prepare the worksheet Open a new, blank worksheet. is =IF(OR($B2>EOMONTH(G$1,0),$C2EOMONTH(G$1,0),EOMONTH(G$1,0),$C2)-IF($B2>G$1-DAY(G$1)+1,$B2,G$1-DAY(G$1)+1)+1)*$E2Īnd the VBA solution (though not suggested) is Sub doCalc()ĭim TCVRng As Range, SdtRng As Range, FdtRng As Range, TermLenRng As Range, MonRng As Range Row 1 contain any day of the months form G1 to CX1Īnd the formula for to be entered in G2 and copied from G2 to CX.Note: the instructions below do not teach you how to format the worksheet. You have to provide the function of basic information, including loan amount, interest rate, and payment duration, and the function will calculate the payment. The worksheet contains the following ActiveX controls: two scrollbars and two option buttons. As all the formulas and data sheets are already arranged this way you dont need to. Select Close and Return to Microsoft Excel. This is an automated template that will help you to save a lot of time. Paste the code in the area between, Public Sub LoanTable and End Sub. Rev per day formula is = (Deal value/365)/(Term length/12) in Column E Loan Calculator This page teaches you how to create a simple loan calculator in Excel VBA. Copy the code and paste into the VBA window in Excel.This is a non VBA solution considering the following